Field Format

jarett

Board Regular
Joined
Apr 12, 2021
Messages
87
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,372
Office Version
  1. 365
Platform
  1. Windows
What application/program are you using to view the CSV file contents after exporting the file?
 

jarett

Board Regular
Joined
Apr 12, 2021
Messages
87
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,372
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 12, 2021
Messages
87
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,372
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 12, 2021
Messages
87
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,372
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 12, 2021
Messages
87
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,372
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,136,864
Messages
5,678,210
Members
419,751
Latest member
richkings

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top