Export Excel CSVs with Double Quotes

The contents of this article may be out of date. It has been archived and will no longer be updated, comments are closed and the page is provided for reference purposes only.

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 Litres 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")

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 Litres 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.

This article was posted on 09 April 2010 in Excel, Magento

comments

What you have had to say about all this...

Great article. Thank you for taking the time to write this. I did have a problem with the date column - the macro whilst converting everything, did not honour the yyyy-mm—dd format - and reset it - MySQL can be very picky with this!

- lyn

Thanks pal its really a very useful information

- boat parts

You legend - thanks a lot - can’t believe Microsoft can’t manage to add something like this!

- Matt Jensen

Wow…neat work…did i say Wow earlier…

- Ashutosh

Excellent! Exactly what I was needing for a tedious project.

- Steve Kincaid

This is a very handy tip, you’ve saved me alot of time and effort. Thanks

- Peter

OpenOffice Calc will offer to save as Text CSV and let you pick text field quotes and delimiter types.

- Mike Leidel

Hi There

Seems like a really clever solution. I have follows all steps. When I run the macro I get a Save As pop-up and save the file under a new filename.csv. The macro runs without any errors but when I open filename.csv no double quotes are shown around cell contents. Any thoughts on what I may be doing wrong?

Thanks, Ian

- Ian Ryan

This works very well, thank you. Is there a simple modification that will leave the values in number fields without the quotes?

- J H Boyd

Thank you! Exactly what I was looking for.

- Anita

The script ran great on one line! It will save me a lot of grief, however i am getting a
run time error “6”:
overflow
Any ideas what this could be, I should mention i am a complete Newbie.

Thanks again for this!!

- Ronnie Kerr

Thank you!
Please find below slightly modified version, processing NULL values, substituting double quotes as well.

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
If (CurrCell.Value = “NULL” Or Len(CurrCell.Value) < 1) Then
’ Accomodate for NULL values, presented as string
CurrTextStr = CurrTextStr & ListSep
Else
CurrTextStr = CurrTextStr & “”“” & Replace(CurrCell.Value, “”“”, “”“”“”) & “”“” & ListSep ‘Replace double quote with 2 double quotes for values
End If
Next
While Right(CurrTextStr, 1) = ListSep
CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
Wend
Print #1, CurrTextStr
Next
Close #1
End Sub

- val

Doesn’t seem to work in Excel 2007.
Throws error at
Open FName For Output As #1
Any ideas??

- Travis

This works ok

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
Dim FNum As Integer
FNum = FreeFile

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 #FNum
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 #FNum, CurrTextStr
Next
Close #FNum
End Sub

- Travis

Thanks so much for sharing this - it let me quickly convert a file and and saved a lot of mucking around!

cheers

- Bob Goyetche

Really meaningful article! It’s all critical piece of info in export excel CSVs with double quotes issue. Thanks for published!

- Kelly Preet

These scripts give back a syntax error in Excel for Mac 2011.

Any help to fix?

Never used excel macros before.

Thanks!

- Tom

Wow, thank you guys so much!

The similar script MS has provided didn't work at all (surprise, surprise!), it actually emptied my original sheet!

Your script worked wonderfully, except for neglecting double-quoted inside cell content.
How happy I was to discover this was answered in the comments!

Of course some modifications had to be done, as the comments-engine replaced the straight-double-quotes (and single-quotes) with slanted ones, plus some line-feed/return problems - but these were easy to solve!
(howto: replace all the wrong double-quotes; and delete the wrong line-feeds, replacing them with “enter” inside the VB editor - it’s easy to see the red lines).

Again, thanks heaps!

- yudayuda

That's the end of this article. I hope you found it useful. If you're enjoyed this article why don't you have a look around the archives, where you can find some more tutorials, tips and general ramblings.