How to prevent excel from truncating numbers in a CSV file?

Question

The first few lines of my CSV file look like this (when viewed from Notepad ++):

Trace, original serial number, new serial number 0000073800000000097612345678901234567890,0054,0001

When I open this file in excel, I get the following:

For some reason, excel truncates the serial numbers and the trace number. I tried changing the format to Text , but this still does not work, since excel only sees the value up to 6:

7.38000000000976E + 34

If I change it to Number :

73800000000097600000000000000000000.00

What can I do? I have only 60 lines, so if I need to start over and some need to rewrite text in excel, I will, but I'm afraid that saving will change the format again.

Answer 1

You do not need to start or modify an existing CSV. The fastest way is to use the Excel Text Import Wizard. On the data tab in the Get External Data section, click From Text and select the CSV file.

The wizard that appears allows you to tell Excel the data type of each column, and you can tell it to use text for your barcode.

Answer 2

Excel tries to "help" you by formatting the input values. To avoid this, do not double-click the file to open it. Instead, open the Data tab, and in the Get External Data section, click From Text

Tell the import wizard that the fields are text:

Answer 3

One solution that may work for you depending on the environment in which you use CSV, you can add a non-digital character to the beginning and end (for example, "_") values. This will make Excel recognize it as text. You can then remove the “_” characters in your downstream environment (SQL, data blocks, etc.), or even leave them if they do not affect your reports.