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

Excel is trying 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 on From Text

Then tell the Import Wizard that the fields are Text:


One solution that may work for you depending on the environment you consume the csv, you can add a nonnumeric character to the beginning and end (e.g. a "_") of the values. This will force Excel to recognize it as text. You can then remove the "_"s in your downstream environment (SQL, Databricks, etc.) or even keep them if they don't interfere with your reporting.


You shouldn't need to start over or alter the existing CSV. The fastest way might be to use Excel's text import wizard. In the data tab under Get External Data click From Text and select your CSV file.

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

Tags:

Csv

Excel