Excel export with specific csv settings?

Question

I'm a newbie to excel and csv files.

I have a excel file with the following structure … http://cl.ly/Rdkp

user_login  user_email  user_pass   first_name  last_name   display_name    role
Some Name   [email protected]      Some    Name        subscriber

You can see the real structure on the screenshot posted above.

However I need to export this data as a .csv file with the following structure …

"user_login","user_email","user_pass","first_name","last_name","display_name","role"
"johndoe","[email protected]",,"John","Doe","John Doe","administrator"

So this means I need to wrap all my data in my excel tables inside quotes "" and separate them by comma.

I'm on a mac and currently using numbers, where I can't find an option to export comma-separated only with ; as separator. I'd also be able to use Excel.

Any idea how to do what I need. In the first place I wonder how I can wrap every cell inside quotes so it matches my desired output format.

Thank you in advance, Matt

Answer 1

This is not my solution as I found this on someone else's article available here: http://www.markinns.com/articles/full/export_excel_csvs_with_double_quotes

Looks like the only method is to use VB Macro that actually check and append double quotes as needed. I did test the code by running it, and when you run the code it will prompt you to save the CSV file straight away, and the content of the CSV file will have the double quotes as needed.

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