Data corruption issue when editing .csv files with long numbers using scientific notation

Question

I'm editing .csv files with Excel, and in some case it contains long numbers (e.g. 800016463473935), which when working in Excel and automatically turned into scientific notation - however what's persisted in the .csv file is not the correct number, but the actual scientific notation value (e.g. 8E014), actually resulting in data corruption. Any workaround?

Answer 1

Hello,

Let's work on this together. Follow these step for you to import your .csv data to Excel without being converted to scientific notations:

1. Open your .csv on a text editor like Notepad or Wordpad.
2. Save it as a .txt file.
3. Open your text file in Excel. Excel will start the Import Text Wizard.
4. On step 1 of the wizard, select Delimited as your Original data type.
5. Click on Next.
6. On step 2 of the wizard, make sure that you tick all the boxes under the Delimiters section.
7. Hit Next.
8. Under Column data format, select Text from the options. Make sure that you do this for each of the columns under Data preview.

9  Click on Finish.

Your file should be imported as it is, rather than converted into scientific notations.

Update us on the result.

Thank you.

Answer 2

Yep - but it's easy to forget and eventually leads to data corruption. Not sure how to file a bug, but I think it qualifies as a bad one!

Answer 3

BLBDC,

Did you try formatting the problematic cells as number with no decimal places?   That might clear up the problem.

--

Regards,

Tom Ogilvy

Answer 4

It does help, but when the file is saved as .csv, then the formatting preference is gone, and next time the file is opened the scientific notation is forced again on the number, leading to the data corruption when saving.

Answer 5

Thanks Aldwin. In my case, opening the .csv file with Excel doesn't start the Import Text Wizard, it just opens the file. (note: this is on MacOS, with latest Excel 64 bits release).