We have an application that takes data from an enterprise system & will extract data into a flat, CSV file. We have a product that functions with excel in which you can generate all sorts of excel reports (it uses add-ins to do this stuff). In the middle, we've got SQL 2008, which uses these flat, CSV files, and creates data cubes in SQL, and then pushes out specific files to Excel.
That said, recently, I took one of these CSV files & pulled it into Excel to filter the data & see what may have been causing an issue downstream. I filtered it, then saved it to a CSV file & then ran it thru the product.
The bad news is, that in some of the number fields, Excel scrambled the data in there.
The field in question is a UPC Code field, that in our case, can be 18 characters long.
What Excel did in my case was to do one of three things:
1. It left the UPC code field alone,
2. It gave the UPC Code field a Scientific notation (1.109843E+17), or
3. it simply "rounded" the right most 3 digits (best I can tell) of that field.
I lost 1/2 the day last Friday chasing those 3 digits on a few thousand UPC codes where excel simply "rounded" out any distinction between them.
This same issue has hit at least two other people this week.
Is there any setting, code or anything else to allow Excel to open, filter (or anything) and re-save this data without it changing it?
Not sure if this needs to be in another forum or not, but thought that it may be applicable to programming if there was code needed to change it on open.
Any help appreciated.
That said, recently, I took one of these CSV files & pulled it into Excel to filter the data & see what may have been causing an issue downstream. I filtered it, then saved it to a CSV file & then ran it thru the product.
The bad news is, that in some of the number fields, Excel scrambled the data in there.
The field in question is a UPC Code field, that in our case, can be 18 characters long.
What Excel did in my case was to do one of three things:
1. It left the UPC code field alone,
2. It gave the UPC Code field a Scientific notation (1.109843E+17), or
3. it simply "rounded" the right most 3 digits (best I can tell) of that field.
I lost 1/2 the day last Friday chasing those 3 digits on a few thousand UPC codes where excel simply "rounded" out any distinction between them.
This same issue has hit at least two other people this week.
Is there any setting, code or anything else to allow Excel to open, filter (or anything) and re-save this data without it changing it?
Not sure if this needs to be in another forum or not, but thought that it may be applicable to programming if there was code needed to change it on open.
Any help appreciated.
