Field Format

[jarett]

I thought I could figure this out from one of my previous threads, Access Export to Txt Decimals, but it seems it is not working. So I have a field with a UPC number and when I run the query in Access the number displays correctly. I tried formatting the field by naming it UPC, field I am pulling from is [UpcCode] then used the STR function - UPC: Str([UpcCode]). When it exports to the csv file the column is formatted as "general" and the UPC number has a decimal with the + sign.

Ex. in the query the UPC field has 955525294819 but in the csv file it comes out as 9.55525E+11

 

[Joe4]

What application/program are you using to view the CSV file contents after exporting the file?

 

[jarett]

Excel Office 16, the csv file I am exporting is getting uploaded to a FTP site, if that makes a difference. And all of this is being done through batch files and task scheduler.

 

[Joe4]

Excel Office 16, the csv file I am exporting is getting uploaded to a FTP site, if that makes a difference. And all of this is being done through batch files and task scheduler.

Just as I suspected.

If you want to view a CSV and truly see what it contains, do NOT use Excel to view it!
Excel itself does conversions on the data as you open it, so does NOT give you a true representation of the CSV content. It will convert anything that looks like a number, to a number, and if it is a large number, it will show it to you in Scientific Notation.

If you truly want to see what the data contained in a CSV looks like, use a Text Editor like NotePad, WordPad, or one of the many 3rd party offerings.
This will show you truly what the data inside your CSV looks like.

This is one of my biggest pet peeves with the Microsoft Office Suite. Choosing Excel as the default program to view CSV files is a poor option. One of the first things I do when I get a new computer is to change that default program to NotePad instead.

So try exporting your file from Access to a CSV file and open it in NotePad, and see what the data looks like then.

 

[jarett]

Just as I suspected.

If you want to view a CSV and truly see what it contains, do NOT use Excel to view it!
Excel itself does conversions on the data as you open it, so does NOT give you a true representation of the CSV content. It will convert anything that looks like a number, to a number, and if it is a large number, it will show it to you in Scientific Notation.

If you truly want to see what the data contained in a CSV looks like, use a Text Editor like NotePad, WordPad, or one of the many 3rd party offerings.
This will show you truly what the data inside your CSV looks like.

This is one of my biggest pet peeves with the Microsoft Office Suite. Choosing Excel as the default program to view CSV files is a poor option. One of the first things I do when I get a new computer is to change that default program to NotePad instead.

So try exporting your file from Access to a CSV file and open it in NotePad, and see what the data looks like then.

I guess the viewing part is not the issue though, because we do not need to view it, once the csv file is uploaded to the FTP site it is uploaded again into an ERP system, so when it gets into the ERP it is still in the scientific notation format. So there's no way to format the export specification correct?

 

[Joe4]

So there's no way to format the export specification correct?

If the field being exported is a String/Text field, I think it should export correctly. If not, try rebuilding your Export Specification.

Can you try exporting a fresh, new CSV file and view it in NotePad and confirm what the data looks like there?
If it looks fine there, then something is happening along the way AFTER it has been exported that is changing it.

I actually had an similar to this with a client years ago. Then were sending me SSN in scientific notation. They SWORE that they were exporting it as Text.
After much back-and-forth, we determined what the issue was. The person in HR who was actually uploading the files was opening them in Excel first to spot check them, and then saving them!
So, they unknowingly were converting the files before they got to us.

 

[jarett]

Ran the export and opened the csv file in notepad this is an example of one of the records. The red text is the UPC field, looks correct in notepad, then I went open the csv file and yes it is in the SN format. I removed the text qualifier just to see if it did something.

5/25/2021 0:00:00,0061503,48.00,FRT-HJEXXX,2XLR NAVY, 955525294819,Action Specialties L.L.C.,7915 Hwy 90 W,,,New Iberia,LA,70560,USA,,,jarett@actionspecialties.com

 

[Joe4]

Ran the export and opened the csv file in notepad this is an example of one of the records. The red text is the UPC field, looks correct in notepad, then I went open the csv file and yes it is in the SN format. I removed the text qualifier just to see if it did something.

5/25/2021 0:00:00,0061503,48.00,FRT-HJEXXX,2XLR NAVY, 955525294819,Action Specialties L.L.C.,7915 Hwy 90 W,,,New Iberia,LA,70560,USA,,,jarett@actionspecialties.com

That means Access is exporting the file correctly (notice it is NOT in Scientific Notation).
So whatever the issue that is occurring is happening AFTER the Access export.

I would recommend checking it each step along the way, being sure to use NotePad and no one opens it in Excel along the way (though it should only be an issue if someone opens it in Excel and re-saves it).
Perhaps it is being altered in the FTP transmission of the file. I believe that there are different methods of transmitting data via FTP and perhaps some alter the data.
Or perhaps like I had happen to me, someone was opening and saving the file along the way.

 

[jarett]

That means Access is exporting the file correctly (notice it is NOT in Scientific Notation).
So whatever the issue that is occurring is happening AFTER the Access export.

I would recommend checking it each step along the way, being sure to use NotePad and no one opens it in Excel along the way (though it should only be an issue if someone opens it in Excel and re-saves it).
Perhaps it is being altered in the FTP transmission of the file. I believe that there are different methods of transmitting data via FTP and perhaps some alter the data.
Or perhaps like I had happen to me, someone was opening and saving the file along the way.

What steps are you referring to check it? As soon as I export it I check it in notepad, so it is not from someone opening it or saving it. When I export it to a xlsx file it is correct even when viewing in excel.
I am using a WINSCP script to transfer the csv file, could this be an issue?

 

[Joe4]

What steps are you referring to check it? As soon as I export it I check it in notepad, so it is not from someone opening it or saving it. When I export it to a xlsx file it is correct even when viewing in excel.
I am using a WINSCP script to transfer the csv file, could this be an issue?

I mean, have someone of the other end of the FTP transfer open it up in NotePad and check it after the transfer.
If it is problematic at that point, then it appears that the issue would be with the FTP transfer.
If it is still good then, then the problem is on their side, and they need to figure out what they are doing wrong, as they are receiving the file in good condition.