Network Automation Software We are currently migrating this forum
over to our HelpSystems domain. Please
post all new threads in our new
HelpSystems Community Portal.
Post to the HelpSystems Forum
You are not currently logged on. You must be logged on in order to post. Log on
Or Create a new account
AutoMate Discussion
Decrease font size
Increase font size
Topic Title: Variable erroneously rounding
Topic Summary:
Created On: 01/21/2013 12:25 PM
Status: Read Only
Linear : Threading : Single : Branch
Search Topic Search Topic
Topic Tools Topic Tools
View similar topics View similar topics
View topic in raw text format. Print this topic.
Answer This question was answered by JeanDelfosse, on Tuesday, January 22, 2013 3:09 PM

Answer:
Brian,
that was exactly what I was after when I asked you about the csv separator (,) and the decimal separator (.), and the samples of the csv file.

If the Jet engine determines the data type by scanning too few rows of data, you could add in the schema.ini file one line
MaxScanRows=0
that will instruct the Jet engine to read ALL the rows of data to determine the data type.
If within some row the field is a decimal, it will hopefully be taken into account.
The default MaxScanRows is in the registry at
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text
In my system it is equal to 25. What about yours ?
Anyhow MaxScanRows=0 would avoid the problem.

Could you add that in the schema.ini and let me know ?
 01/21/2013 12:25 PM
User is offline View Users Profile Print this message

Author Icon
BrianPearson
Expert (100-199)

Posts: 149
Joined: 04/08/2005

We read from a .csv file, and evaluate the data, then re-create the file with the evaluated data.

we do this with 3 different files, all the same structure.

for some reason, on one of the files, we are rounding a variable off without intent, the other 2 work fine and the way we expect.

the variable looks like %ds.[Temp Rate]% when we either show it in a dialogue, or write it to the file, it's taking information from the original .csv as = '8.50' and re-writing it as '8'

any idea why this might be happening?

we also tried forcing it to have two decimal places by using %round(ds.[Temp Rate],2)% - same result (which we kind of expected ... but are trying everything)

 Category Survey
 01/21/2013 08:52 PM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

rian,
if you can run it step by step (or pause it at the relevant step) in task builder, what is the value of ds.[Temp Rate] displayd in the "variables" pane ?
If it is 8, this is an SQL problem

Could you provide a working sample that demonstrates the case ?
At least the csv file (or an excerpt of it) ?
What is the fields separator in the csv ?
What is the decimal separator in the 8.50 number (I mean in the csv file)
What is the decimal separator in your locales ?

 Category Survey
AutoMate BPA Server 8 version:
Windows version:


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
 01/22/2013 11:11 AM
User is offline View Users Profile Print this message

Author Icon
BrianPearson
Expert (100-199)

Posts: 149
Joined: 04/08/2005

Jean, thanks for your reply. your first sentence brings up something that I've seen more and more of ... when I'm looping a dataset, I don't see anything displayed in the variables pane. I can put up a dialogue that shows info from the current row ... but it seems maybe when i've done more than one loop it stops displaying? is there a setting i can change to fix that?

attached an excerpt of the .csv, when you ask about the field separator, i'm hoping that's a trick question ... wouldn't it be comma by it's nature?

not sure what you mean about the decimal separator in the 8.50 num?

don't know where to find the info in locales, i looked in system but didn't see anything.

i guess the thing that has me truly perplexed is that the files seem nearly identical. one file works, the other doesn't.

when i run the same process on example2.csv it works great, example 1 cuts off the decimal.

I made a simple loop to test it, and it came back with the same result, works against example2, but not example1

 Category Survey



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)


example1.csv
example1.csv  (0 KB)
example2.csv
example2.csv  (0 KB)

 01/22/2013 11:47 AM
User is offline View Users Profile Print this message

Author Icon
BrianPearson
Expert (100-199)

Posts: 149
Joined: 04/08/2005

i guess i answered one simple question that i posed ... why don't i see the dataset displayed in the variable pane ... and it looks like it's because the field names have spaces in them. so the variables with spaces are displayed (and accessed) by ds.[co code] instead of ds.cocode ... when i remove the spaces they show up.

is that by design or a bug?

 Category Survey
 01/22/2013 12:06 PM
User is offline View Users Profile Print this message

Author Icon
BrianPearson
Expert (100-199)

Posts: 149
Joined: 04/08/2005

wow ... well ... we figured it out, but it's nasty.

it turns out that it's NOT the other files that are correct ... it's that the other files both had an initial row that has a decimal in it. so when we use the SQL query against the .csv, it recognizes that field as a decimal ... and all other values following qualify under the same type ...

so now we're going to have to figure out a way to cast that field as a decimal

 Category Survey
 01/22/2013 02:02 PM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

Answer Answer
Brian,
that was exactly what I was after when I asked you about the csv separator (,) and the decimal separator (.), and the samples of the csv file.

If the Jet engine determines the data type by scanning too few rows of data, you could add in the schema.ini file one line
MaxScanRows=0
that will instruct the Jet engine to read ALL the rows of data to determine the data type.
If within some row the field is a decimal, it will hopefully be taken into account.
The default MaxScanRows is in the registry at
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text
In my system it is equal to 25. What about yours ?
Anyhow MaxScanRows=0 would avoid the problem.

Could you add that in the schema.ini and let me know ?

 Category Survey
AutoMate BPA Server 8 version:
Windows version:


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
 01/22/2013 02:40 PM
User is offline View Users Profile Print this message

Author Icon
BrianPearson
Expert (100-199)

Posts: 149
Joined: 04/08/2005

Jean,

once again your superior intellect and VAST knowledge of everything that we need to know has been proven!

Excellent answer, and it works perfectly!

Too bad you don't live in the continental US, we'd love to buy you a meal.

 Category Survey
 01/22/2013 09:28 PM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

You're welcome, Brian.
I'm happy helping you when I can

 Category Survey
AutoMate BPA Server 8 version:
Windows version:


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
Statistics
18258 users are registered to the AutoMate Discussion forum.
There are currently 0 users logged in.
The most users ever online was 5551 on 01/08/2018 at 11:11 AM.
There are currently 1139 guests browsing this forum, which makes a total of 1139 users using this forum.

FuseTalk Enterprise Edition v4.0 - © 1999-2020 FuseTalk Inc. All rights reserved.

Sitemap Network Automation Software Blog