Help Search our knowledge base:

Index - FAQ - Misc
 Details
Frequently Asked
Question (FAQ)
Document Number: 1085

Optimize for Printing
E-Mail This Link
 Feedback
This document ...
helps resolve my question or problem.
does not apply to my question or problem.
is inaccurate for my question or problem.
My CSV does not show proper information when opened in Microsoft Excel

Microsoft does extra processing to "best guess" your data when you simply double click on a CSV file. Or, when you use the standard "Open" feature.

This best guessing can cause several negative side effects
  1. Large numbers are improperly written in scientific notation, this includes credit card number fields
  2. Leading zeroes may be removed, this includes zip code fields
  3. Some fields may display inaccurate information
  4. Some fields display errors like #NAME?
To avoid these problems open your CSV files in Excel using these instructions:
  1. Open Excel 2003
  2. Open a blank workbook
  3. Select the "Data" menu
  4. Click on "Import External Data"
  5. Click on "Import Data"
  6. Find your file and click "Open"
  7. Make sure to select "Delimited", and "UTF-8" then click "Next"
  8. Select "Comma" as your delimiter (uncheck all other delimiters)
  9. Select the double quote ( " ) as your text qualifier
  10. Click next
  11. Scroll the "Data preview" window all the way to the right
  12. Hold down the "Shift" key and click on the last column
  13. Select "Text" from the column data format
  14. Click "Finish"
  1. Open Excel 2007
  2. Open a blank workbook
  3. Select the "Data" tab
  4. Click on "From Text"
  5. Find your file and click "Open"
  6. Make sure to select "Delimited", and "UTF-8" then click "Next"
  7. Select "Comma" as your delimiter (uncheck all other delimiters)
  8. Select the double quote ( " ) as your text qualifier
  9. Click next
  10. Scroll the "Data preview" window all the way to the right
  11. Hold down the "Shift" key and click on the last column
  12. Select "Text" from the column data format
  13. Click "Finish"
  1. Double click to open the document in Excel 365
  2. right click on the credit card (field AP) header field and select "format cells"
  3. choose "number" and set it to 0 decimal places
  4. click ok
  5. make sure to widen the column to be able to see the full number

These instructions were written for Microsoft Excel 2007. Instructions for importing into other versions of Excel may vary.

Note: If you forget to choose Unicode/UTF-8 when importing that many international characters will not be imported properly.