CSV Query Problem

Technical support and scripting issues

Moderators: Dorian (MJT support), JRL

Post Reply
AndrewT
Junior Coder
Posts: 38
Joined: Thu Sep 17, 2015 6:06 pm

CSV Query Problem

Post by AndrewT » Mon May 04, 2020 5:03 pm

Has anyone else had a problem when querying a CSV file that small negative numbers return as zero? Or truncating values in particular columns?

I have a CSV file that has a column with mostly zeroes in it, with the occasional small negative decimal (ie. -0.15). When I do a Sum of that column, the result comes back 0. When I query the whole table and look a the specific location where I know there should be a small negative value, the result is still 0.

Other columns in the CSV have positive and negative decimal numbers and they all work fine. When I change the small negative decimal in the column in question to something bigger (ie. -10.15), the result I get is 10.

I am baffled as to how/why I am getting truncated numbers in just one column.

Andrew T.

AndrewT
Junior Coder
Posts: 38
Joined: Thu Sep 17, 2015 6:06 pm

Re: CSV Query Problem

Post by AndrewT » Mon May 04, 2020 6:47 pm

***SOLVED***
The answer is to use a Schema file. I believe that CSV queries without schema files default to the format of the first line of data. Since the first row of the column in question was 0, and not 0.00, the default became integer instead of decimal.

Andrew T

User avatar
Marcus Tettmar
Site Admin
Posts: 7378
Joined: Thu Sep 19, 2002 3:00 pm
Location: Dorset, UK
Contact:

Re: CSV Query Problem

Post by Marcus Tettmar » Tue May 05, 2020 4:06 pm

Glad you sussed it. Using schema files was discussed here:
https://www.mjtnet.com/blog/2010/01/28/ ... s-as-text/
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar

Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts