10

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 this:

enter image description here

For some reason, excel is truncating the serial numbers and the trace number. I have tried changing the format to Text but that still doesn't work, as excel only sees the value up to the 6:

7.38000000000976E+34

If I change it to Number:

73800000000097600000000000000000000.00

What can I do? I only have 60 lines, so if I have to start over and some how recopy the text into excel I will, but I'm afraid saving it will change the format once again.

AdamMc331
  • 16,492
  • 10
  • 71
  • 133
  • 1
    Hi. You did a good move to convert the column to TEXT. Just wide it up. You can create empty XCEL document with TEXT field from the beginning and try to paste the data – volkinc Jan 06 '15 at 14:59
  • Changing the size once I changed it to text didn't work, @volkinc. I will try to redo the file with `Text` format and see if it works then. – AdamMc331 Jan 06 '15 at 15:00

3 Answers3

11

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.

Jobbo
  • 1,358
  • 1
  • 8
  • 21
  • This worked, thanks! If I format all cells and change this to .xlsx now, when another person opens it should they see the expected format? – AdamMc331 Jan 06 '15 at 15:07
  • Yes once you 'save as' an XLSX, the formatting should be preserved – Jobbo Jan 07 '15 at 07:26
  • 1
    Five year latter and this answer is still useful! Maybe Microsoft shouldn't make things so hard. Thank you! – dsenese Aug 24 '22 at 16:52
6

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:

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
1

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.

Scott
  • 11
  • 1