CSV issues with formatting

ClaireGoode

New Member
Joined
Jan 6, 2017
Messages
13
Hi

We have recently moved to a new (external) system and in order to load our data we have to send the supplier a CSV file.

We create the data in Excel and then Save As CSV. However when you then close and re-open the CSV file many of our numbers convert to Scientific format (things like customer account numbers, phone numbers, etc) and this is what gets imported into our system.

I have tried so many options and read some many posts about this but so far cannot find a solution. As I've said the only format our system will accept is CSV so we can't use the option of .txt (which I know can alleviate the issue). I have also tried adding an apostrophe before the numbers, and converting the columns to text (using Text to Columns Wizard) and converting the columns to numbers (and removing the decimal places).

I had another issues with Chinese characters but have resolved this by saving CSV from .txt and changing the endcoding to UFT-8 which I had hoped might help with the numbers issue too but it didn't!

I am by no means an Excel expert so want to avoid anything like VBA/script writing to stop this happening. Does anyone have any solutions for a mere mortal to use?!

TIA

Claire.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
We create the data in Excel and then Save As CSV. However when you then close and re-open the CSV file many of our numbers convert to Scientific format (things like customer account numbers, phone numbers, etc) and this is what gets imported into our system.
What are you using to open and view the CSV file you are creating?
Don't use Excel to view the CSV file. Excel automatically does conversions on the data based on the format it THINKS it should have. Hence, Excel is not a reliable program to view how the data actually looks in the CSV file.
Try opening and viewing the CSV file with a Text Editor like NotePad. If you do that, does the data appear to be in the correct format? If so, you are fine.

It is a major pet peeve of mine that Windows sets Excel to be the default program to view CSV files. I always change that on my computer. I always tell people NEVER use Excel to view your CSV files, if you want to see what the data in your CSV really looks like.
 
Upvote 0
Thanks for your reply.

When I open the file in Notepad the data is fine, but when I send the file as a CSV to our suppliers the data loaded is this scientific format. They don't have any answers for this as they claim to have never seen this issue before.
 
Upvote 0
When I open the file in Notepad the data is fine, but when I send the file as a CSV to our suppliers the data loaded is this scientific format. They don't have any answers for this as they claim to have never seen this issue before.
If, when you look at the CSV in NotePad, the data looks fine, then the data is fine. The issue would then be on their side, precisely what they are doing with the file, how they are loading it. If they are using Excel to view it, they would have the same issue I mentioned earlier.

I had an issue with a client like this years ago. They were sending me a CSV file that was missing leading zeroes from Social Security Numbers. They put me in touch with their IT department. We went round and round, as the IT department was telling me that the leading zeroes were there on the CSV file that they were creating, but I wasn't seeing them, even when viewing in NotePad. After much back and forth, we finally found the source of the problem - the HR person that was sending me the files was opening them first in Excel to check them, then Saving them and sending them to me. So by saving it after opening it in Excel, they were saving the data field conversions that we didn't want to happen!

So in a nutshell, the moral here is:
- Don't use Excel to view a CSV file, if you want to know exactly what the data inside really looks like
- Don't use Excel to edit CSV files

Its fine to use Excel to create a CSV file, but after that part is complete, it is best to not use Excel to view or edit the CSV file.

Back to your problem, if the data is not in scientific notation when you view it in NotePad, the problem is not on your side, but your clients. They are doing something wrong in processing/loading your file. Either that, or that is just how their system shows large numbers. Either way, other than changing your number to text and enclosing it in text qualifiers, there isn't really anything you can do on your side.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,190
Members
452,616
Latest member
intern444

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
Back
Top