Correcting Problems with CSV files

There are typically 3 things that can cause problems during the import process. This document will give you some insight on correcting the problems we see most.

  1. Unknown/Special Characters:
    Many times special characters in the CSV file can throw off the import process. One option is to load the CSV file in Microsoft Excel and choose "Save As..." then change the Save As Type to "CSV (MS-DOS) (*.csv)" - This option saves the CSV file in standard ASCII without special characters.



  2. Incorrect Number of Columns:
    One of the major challenges with Microsoft Excel saved files is that it can interpret blank cells as having no value.  When exporting/saving to a CSV file it may choose to not export cells giving you an in accurate number of columns in a row.  

    Example: 
    "First Name","Last Name", "Phone Number"
    "John","Smith","555-1212"
    "Mary","Smith"
    "Sam","Smith","555-1313"

    Notice: Mary Smith does not have a phone number and the number of columns for her record is only 2 and we are expecting 3 - this will cause a problem with the import.

    To correct this problem you can add a "dummy" 4th column (last column of your data) and put a value in it for all records - you can call it anything, I usually call it "Plug"

    "First Name","Last Name", "Phone Number","Plug"
    "John","Smith","555-1212","Plug"
    "Mary","Smith","","Plug"
    "Sam","Smith","555-1313","Plug"

    Notice: By adding a cell at the end, Microsoft Excel is forced to export all blank cells up to that last column.  Mary Smith now has a blank cell where the Phone number should be.

    The easiest way to fill that last column is to enter the word "Plug" into the header cell to the right of the last column of your CSV file and press [Enter].
    Then click back into that cell to highlight it.  The cell will now have a box around it with a "Handle" in the lower right corner which you can click/drag to the last row of your CSV file.  This will copy the word "Plug" into each Cell from the Header all the way down to the last selected row.  Once you have made the changes the do a "Save As..." and give it a new name using the file type of "CSV (MS-DOS) (*.csv)"



  3. Duplicate Headers:
    The last challenge that I see occasionally is that the column headers of the CSV file are not Unique.  Each column MUST have a unique name in order for the file to be imported correct.  if you have 2 columns with the name of "Phone" then name one "Phone1" and the other "Phone2" or what ever you desire as long as there are no 2 columns containing the same name. 
Was this article helpful?
1 out of 1 found this helpful
Have more questions? Submit a request

Comments

Please sign in to leave a comment.