SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Jul 2007
    Posts
    233
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exporting .csv file - Large numbers being converted to exponent format

    Greetings,

    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?

    Thanks
    Kind regards

  2. #2
    SitePoint Wizard wonshikee's Avatar
    Join Date
    Jan 2007
    Posts
    1,223
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Addict
    Join Date
    Jul 2007
    Posts
    233
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the response.

    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.

  4. #4
    SitePoint Wizard wonshikee's Avatar
    Join Date
    Jan 2007
    Posts
    1,223
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by peppy View Post
    Thanks for the response.

    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.
    You can do something like

    ISBN: 139045193021311

    Then on the import script, strip the ISBN: out.

    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)

    These are the only two options you really have.

  5. #5
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,870
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    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.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  6. #6
    SitePoint Addict
    Join Date
    Jul 2007
    Posts
    233
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Let me know what your thoughts or solutions are.

    Thanks
    Kind regards


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •