I am exporting items using PHP from MySQL into a CSV file. One column is ISBN 13 digit number and another is UPC 12 digit number.
When I open the exported CSV, my ISBN number 9704322318673 shows up like: 9.70432E+12 . When I re-import this file, this incorrect format also gets imported back to my database rather than a valid ISBN or UPC code number.
Does anyone know how to fix this? Is there a configuration I can use during file export to tell Excel not to format long numbers like this into exponents?
There’s no “correct” way that I know of. CSV is designed to plain text (no styling) so Excel will try to guess what kind of field it’s supposed to be. That’s Microsoft for you.
You can insert a non-numeric character at the start or end to force it to treat it as a text field.
The only problem with this is if some non-numeric character was added to an ISBN code during export, it would become an invalid ISBN code if the user re-imports it back to the database. It would also confuse the user when they open their CSV file to edit and all of the codes are different than what they originally created.
I have to agree with you tho, Microsoft can be a real pain… Hopefully there is some solution to this.
Another solution is to save as a .TXT file and have Excel import it, at which point you can tell it to keep the column as Text instead of General (which will auto format)
Is the value wrong in the CSV file itself or just when you open the file in excel? If the latter than it is just a matter of telling excel that the column is text after it is open.
When I open up the CSV file in Excel, and select a cell, the value in the function bar is correct, but the display on the spreadsheet is in exponential format. If I re-format the cells, the correct value is displayed.
If I just open the file, save and close it, and then re-import the CSV file back into my website, the invalid exponential number gets imported rather than my valid ISBN.
This becomes a problem because I have 3rd party sellers importing/exporting products at my marketplace, and I can’t picture the average person figuring out how to reformat their Excel file when they edit products and re-import them into my site.
My Solution:
When someone exports a CSV file, my script automatically adds an equal sign and double quotes around my ISBN (like: =“9704322318673”). When they open the CSV in Excel, this becomes a function and displays just the number. When they save and re-import, my script automatically strips out any equal signs and double quotes from this column. Hopefully Excel is the only program that opens up CSV because of this function method.