CSV issues with formatting

[ClaireGoode]

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.

 

[Joe4]

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.

 

[ClaireGoode]

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.

 

[Joe4]

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.