Save a CSV file with quotes around the values.

When importing a CSV file, I always make sure that there are quotes around all of my values.  It eliminates a lot of issues.  Here is the difference.

Normal:
ID, Name
1, One
2, Two
3, Three

With Quotes:
ID, Name
"1", "One"
"2", "Two"
"3", "Three"

 In order to accomplish this, we use an Excel macro.

1. Open your CSV file in Excel  
2. Create a new macro.  Using Excel 2007, you click on the View ribbon tab and click on the Macros button.
3. Type anything into the macro name box and click Create.  I typed "abc" into the Macro Name box.
4. Open the file attached at the bottom of this post.  Copy the entire contents of the file by pressing CTRL-A and then CTRL-C.
5. Go to the Excel macro editor and erase everything that you see in the Module1 window that is open.
6. Paste the contents of your clipboard into the Module1 windows by pressing CTRL-V.
7. Close the entire macro editor window by clicking on the red X in the upper right corner.  This will close the Microsoft Visual Basic window.
8. You are now back to looking at your Excel spreadsheet.
9. Highlight the data in your spreadsheet.  You must highlight only the specific data; do not highlight entire rows or columns.
10. Click on the Macros button once again.
11. You will now see an item in the list called QuoteCommaExport.  Make sure it is highlighted and click Run.
12. Type in the full path where you want to save your newly created spreadsheet.  You should use something like C:MyFile.csv.  If you are using Windows Vista or Windows 7, you may not be able to save the file to the root of your C: due to security reasons so you may have to put it in a new folder.
13. Once you've clicked OK, your new CSV file has been created and you can use that file to run your import.

Please note that if you open that newly created CSV file again in Excel to modify it, you will need to repeat these steps if you want to save it again.

ExportExcelToCSV3.txt (1.61 kb)