CODEWITHSUNDEEP
excelcsv
Wilo Maldonado
Wilo Maldonado

Reputation: 551

CSV issue with 0

We have an inventory software that exports the inventory data into a csv file which we use excel to analyze. However, I am having issues with some of the serial numbers that are all numbers and for some reason start with zeros.

Example:

  1. "000015"
  2. "0015"

In example 1 and 2, our inventory software treats them as different serial numbers (which makes sense since it's a string and two items should not have the same serial number)... however, once I export it as a CSV, in the column for Serial Number on example 1 and 2, I only see "15" thus creating me issues when dealing with inventory items in excel because of 'duplicate numbers'. I'm guessing the csv is treating that field as a 'number' instead of a 'text' or 'string'.

My questions is... Is the issue with the csv file after its exported and how excel handles it or is the problem with the inventory software on how it prepares the csv file for export?

If this is something with excel, please do recommend ideas on how to fix it.

Thanks

Upvotes: 0

Views: 187

Answers (1)

Wilo Maldonado
Wilo Maldonado

Reputation: 551

I found a way to fix it.

I changed the extension from .csv to .txt and reopened it with excel. And then I was able to specify exactly how each column should be treated. And with the one I was having the problem, I just classified it as 'text' instead of 'general'

Here are the steps from the link below:

I think the best way is to read the file as text by renaming the file extension to ".txt". Then when you open the file a dialog window will come up. Select Delimited file and go through each window. One the 2nd window check only the comma for the delimiter column. Now here is the trick. the third window you can select each field and specify if you want the column to be read in as text or number. Choose Text for the field with large numbers.

Link:

http://social.msdn.microsoft.com/Forums/office/en-US/64c1ee52-eb53-4202-bb27-d9b08d3bd60a/long-number-in-csv-file-appearing-in-scientific-notation-by-default

Thanks!

Upvotes: 1

Related Questions