Export Excel CSVs with Double Quotes

From http://www.markinns.com/articles/full/export_excel_csvs_with_double_quotes

Excel allows you to save spreadsheets using CSV file format. This particularly helpful as you or your client may have data, say a product catalogue, that can be imported into another application. Continuing the example you may want to import this data into a shopping cart system like Magento. This all sounds good so far but Microsoft in their wisdom don’t allow you to alter preferences for the CSV file format. Excel does it’s own thing and if that does not match your way or the software you want to import the data into then things get a little more complicated.

An Example

I have an Excel spreadsheet from a client with 1000’s of products that have columns for Name, Description, Short Description, Category, SKU, Weight, Status, Visibility, Price & Tax Class. I want to import this into my shopping cart so I don’t have to go through and re-key each item manually. If I save the file as a CSV file using Save As the file is saved something like this:

Name,Description,Short Description,Category,SKU,Weight,Status,Visibility,Price,Tax Class;
Large Tin of Paint,”This paint comes in red, green and blue”,1.5 Liters of Paint,Paints, 001,1.5,In-Stock,Visible,10.00, A;

Excel puts double quotes around items that contain a comma (used as the separator) so as to ignore the comma. Excel does not use double quotes for any value without a comma. Using double quotes is common, Magento warns using empty values (as Excel does) can cause problems with the CSV format. So how do we get Excel to export our CSV file using double quotes around all our values to keep Magento happy?

Excel Macros

Microsoft provide access to Visual Basic in the form of Macros from within Excel that allow us to do things Excel can’t manage by itself. To create a VB Macro open the Visual Basic Editor (Alt+F11) then from the menu Insert > Module. This should open a new module code window that you should copy and paste in the following script:

Sub CSVFile()

Dim SrcRg As Range
Dim CurrRow As Range
Dim CurrCell As Range
Dim CurrTextStr As String
Dim ListSep As String
Dim FName As Variant
FName = Application.GetSaveAsFilename(“”, “CSV File (*.csv), *.csv”)

ListSep = Application.International(xlListSeparator)
If Selection.Cells.Count > 1 Then
Set SrcRg = Selection
Else
Set SrcRg = ActiveSheet.UsedRange
End If
Open FName For Output As #1
For Each CurrRow In SrcRg.Rows
CurrTextStr = ìî
For Each CurrCell In CurrRow.Cells
CurrTextStr = CurrTextStr & """" & CurrCell.Value & """" & ListSep
Next
While Right(CurrTextStr, 1) = ListSep
CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
Wend
Print #1, CurrTextStr
Next
Close #1
End Sub

Now close the script editor, back in Excel run your macro from the menu Tools > Macro > Macros (Alt+F8). You should see a macro named CSVFile selected then all you need to do is click Run. A Save As window should appear that will need you to enter a name for your new file and select a location you will remember to save it to.

Your newly saved file will now enclose all your values in double quotes:

“Name”,”Description”,”Short Description”,”Category”,”SKU”,”Weight”,”Status”,”Visibility”,”Price”,”Tax Class”;
“Large Tin of Paint”,”This paint comes in red, green and blue”,”1.5 Liters of Paint”,”Paints”,”001″,”1.5″,”In-Stock”,”Visible”,”10.00″,”A”;

Now you’ll have no more failed Magento imports using this handy macro.