This is a necessary evil for anyone who is working with CSV files manipulating them for data import to Magento (when XML is not suitable). Add the following function (sub routine) to Excel and hit the Macro button to run it. You’ll have no more failed Magento imports with this handy function.
Sub subExportCSV() On Error GoTo subexport_exit Dim strDelimiter As String strDelimiter = "," Dim strQualifier As String strQualifier = """" Dim arrRng arrRng = ActiveSheet.UsedRange.Value Dim f As String Dim i As Long Dim j As Long Dim strTemp As String f = InputBox("Enter a filename for saving", , "c:\test.csv") If Trim(f) = "" Then Exit Sub Open f For Output As #1 For i = 1 To UBound(arrRng, 1) strTemp = "" For j = 1 To UBound(arrRng, 2) strTemp = strTemp & strQualifier & arrRng(i, j) & strQualifier & strDelimiter Next j Print #1, Left(strTemp, Len(strTemp) - Len(strDelimiter)) Next i subexport_exit: Close #1 End Sub
Go have a look! http://www.pctechblog.com/software/excel/exel-export-to-csv-double-quotes