+ Reply to Thread
Results 1 to 10 of 10

Saving as CSV

  1. #1
    Registered User
    Join Date
    08-01-2012
    Location
    Chicago
    MS-Off Ver
    2007
    Posts
    9

    Saving as CSV

    I have a database that I need to convert to CSV format. That part is easy. I'm having a problem with the CSV format changing all of the UPC numbers into scientific notation. I make sure that the column is formatted as TEXT before I save it into the CSV format, but it changes the numbers anyway. The CSV format is necessary for adding the file to an existing one. Any help would be appreciated!

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Saving as CSV

    Try this instead

    - pre-format the column using Custom value of 0 (zero)
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Registered User
    Join Date
    08-01-2012
    Location
    Chicago
    MS-Off Ver
    2007
    Posts
    9

    Re: Saving as CSV

    I tried that, but get the same result. After saving as CSV, when I open the new CSV file, all of the numbers are converted to scientific notation. If I expand the column width, some of the numbers revert back, but not all. Any lead zeroes in the numbers, which are necessary for UPCs, are gone.
    I am attempting to convert an existing DBF file from an old DOS based Point of Sale system to CSV format so that it can be added to an existing file in a new POS system.
    I have also tried adding some non-numeric data to the column in question, but all of the numbers still get changed. I have also tried putting an apostrophe in front of the UPCs, but with the same result.
    Last edited by LiamChgo; 01-11-2018 at 04:33 PM.

  4. #4
    Registered User
    Join Date
    08-01-2012
    Location
    Chicago
    MS-Off Ver
    2007
    Posts
    9

    Re: Saving as CSV

    I do know that in the CSV file, after expanding the column, the numbers that appear normal are the ones that are missing the lead zero, while the numbers that still appear in scientific notation are UPCs that started with a number other than zero. I tried format the column as text in the CSV file, and manually replace the '0 in front of some of the numbers, or simply put an ' in front of the others, but when I save the file, close it, then reopen it, everything reverts back to scientific notation.

  5. #5
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Saving as CSV

    Could you post the files or is the data commercially sensitive?
    - I would like to experiment with the original DBF file -> any intermediate file -> and the csv
    - I do not need big files - just enough data to illustrate all the problems

    What process are you using to convert/import the data?

    Are you using Excel 2007?

  6. #6
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Saving as CSV

    Quote Originally Posted by LiamChgo View Post
    but it changes the numbers anyway
    How do you know this, are you opening the csv in Excel again?

    A CSV is a plain text document, separating groups of data (columns along a row) by the delimiting character ",". Each line break (enter) is another row of data. Since its a plain text document it stores absolutely no formatting, only values. It does not matter what you format it as prior in Excel, only what the actual underlying value is in Excel.

    If you are opening the CSV up in Excel, Excel translates the text file into a table on a sheet in Excel. It then displays the contents of the cell according to whatever format it thinks those values should be.

    The formatting in Excel is just a visual change on sheet, it doesnt change the underlying value.

    To see the true values, open the CSV in notepad. Are they still in scientific notation there? If so the underlying values in Excel pre-conversion to CSV are likely scientific notation.

    Are the UPC numbers completely numeric and not alpha numeric? If completely numeric how many digits?
    Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.

    "I am here to help, not do it for people" -Me

  7. #7
    Registered User
    Join Date
    04-16-2012
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Saving as CSV

    To save a text file as tab-delimited, UTF-8 encoded in Excel:
    1. Choose File >Save as from the menu.
    2. In the 'Save as type' dropdown
    3. Select 'Web Options' in the 'Tools drop down
    4. Select the 'Encoding' tab.
    5. In the 'Save this document as drop-down, select 'Unicode (UTF-8)

  8. #8
    Registered User
    Join Date
    08-01-2012
    Location
    Chicago
    MS-Off Ver
    2007
    Posts
    9

    Re: Saving as CSV

    I'm not really familiar with the CSV format.
    Yes, when I open it up in Notepad, all of the numbers appear to be correct, complete with the zero prefix.
    The UPCs are all standard 12 digit, maybe a few 13 digit as well.

  9. #9
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Saving as CSV

    Quote Originally Posted by LiamChgo View Post
    I'm not really familiar with the CSV format.
    Yes, when I open it up in Notepad, all of the numbers appear to be correct, complete with the zero prefix.
    The UPCs are all standard 12 digit, maybe a few 13 digit as well.
    I wrote a long explanation and lost it, so sorry for the short response to follow.

    You have what you need if you need a csv. Again csv is just a text file, that uses commas as delimiters meaning it stores no formatting. Excel interprets the text file as each comma being a new column. So if you keep it csv dont bother with formats, its a waste of time as they wont be kept. You may consider using a copy of it as xlsx/xlsm and saving to a csv when you need to generate an updated copy.

    Excel only handles 15 digits of precision or 15 digits of significance. You are under that so you should be fine.

  10. #10
    Registered User
    Join Date
    08-01-2012
    Location
    Chicago
    MS-Off Ver
    2007
    Posts
    9

    Re: Saving as CSV

    Thank you all. I've gotten it to work and kept my boss happy.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 06-15-2016, 06:24 PM
  2. [SOLVED] How can I force Excel Saving without showing the box saving status? It is getting stuck
    By trizzo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-09-2015, 07:37 AM
  3. Replies: 11
    Last Post: 08-23-2014, 01:28 PM
  4. Replies: 1
    Last Post: 08-14-2014, 09:50 PM
  5. Saving external links without saving data values
    By john@axiomatix in forum Excel General
    Replies: 2
    Last Post: 06-18-2013, 05:58 AM
  6. [SOLVED] Saving worksheet using a macro. Problem is that it is saving more rows than it needs to
    By Kammphoto in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-04-2013, 10:09 AM
  7. Renaming Active.Workbook and saving it without saving Macro
    By Djwill in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-04-2012, 03:09 AM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1