Magento Forum

How to correctly format a .csv file for Magento using Microsoft Excel
Total Posts:  37
Joined:  2010-07-15

I have noticed that many on the forums are having problems with properly formatted .csv file created with excel and the suggestion is to install OpenOffice. Excel may not be perfect but with a little coaxing it works just fine for the job. Here are my steps to make it work all of which are found with a little bit of help from our friend Google

1. when creating to initial .csv file make sure that when you are done and if you have blank cells you select

Go To Special | Select blanks | Hit OK
In the first cell that you are placed in insert the following formula: ="”
Press CTRL+ENTER ( this will fill all the empty cells)

2. When ready to export I use the following macro: (found here )

Option Explicit

Public Sub OutputQuotedCSV()
Const QSTR As String = """"
Dim myRecord As Range
Dim myField As Range
Dim vFilename As Variant
Dim nFileNum As Long
Dim sOut As String

Get a filename to save as
vFilename Application.GetSaveAsFilename(filefilter:="Microsoft CSV files,*.csv"_
:="Save as CSV with fields in double quotes")

vFilename False Then Exit Sub 'User chose Cancel

nFileNum = FreeFile
Open vFilename For Output As #nFileNum
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells(1), _
Cells(.Row, 256).End(xlToLeft))
sOut = sOut & "," & QSTR & _
Replace(myField.Text, QSTR, QSTR & QSTR) & QSTR
Next myField
Print #nFileNum, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord
Close #nFileNum
End Sub

this will create a .csv file that properly quotes the correct things in your file. (Even if you have html in it)

3. Open with a text editor and save with UTF-8 encoding and hopefully all goes well.

I hope this help other out.

Magento Community Magento Community
Magento Community
Magento Community
Total Posts:  1839
Joined:  2009-06-04

Excel natively supports UTF-8 encoding. It’s just buried a little.

Use Excel 2007 to export UTF-8
Use Excel 2003 to export UTF-8

Access 2003 and 2007 have table export dialogs, choose CSV, then use the Advanced… button and select your encoding.

Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
    Back to top