xlCSV = comma, Save As CSV = semicolon delimited?

[Felix Atagong]

I have daily Excel files to convert in 'European' CSV format, this is delimited with a semicolon.

If I open the file and do a 'manual' Save As CSV my file is allright.

When I write a macro and do a
ActiveWorkbook.SaveAs Filename:=Firma, FileFormat:=xlCSV, CreateBackup:=True
the delimiter is a comma. I tried with CSVWindows but the macro does NOT follow my 'default' windows separator which is semicolon (Excel 2000).

Anyone has a VBA solution or isn't there any?

PS: I can't change all commas in a semicolon afterwards because some of my 'fields' have commas in them, that's why I use semicolon as a separator!

 

[Felix Atagong]

Testing on different PC's made me find out that the problem is not an Excel, or Excel VBA one, but Windows! All my other PC's save CSV files with a semicolon, but only 'this' one saves with a comma. International settings however look allright. Perhaps the registry has been touched, gotta try and find out.

If anyone has a suggestion, please do!

 

[Gates Is Antichrist]

In the U.S. setting, CSV means "Comma Separated Value." I would have thought that European operating systems would make the substitution you want, but see this... http://support.microsoft.com/default.aspx?scid=kb;en-us;Q288839

At any rate, to achieve what you want, you can go to a different worksheet and build your own semicolon delimited data with the ampersand (&) concatenation operator.

E.g. worksheet Sheet4 can contain
A1=Sheet1!A1& ";" &Sheet1!B1& ";" &Sheet1!C1& ";" &Sheet1!D1& ";" &Sheet1!E1& ";" &Sheet1!F1& ";" &Sheet1!G1& ";" &Sheet1!H1& ";" &Sheet1!I1& ";" &Sheet1!J1& ";" &Sheet1!K1& ";" &Sheet1!L1

and copy that down column A. Then save as Text, not as CSV.

You can build the output file in VBA instead of using the Sheet4 above. http://www.dicks-blog.com/archives/2004/11/09/roll-your-own-csv/ may give you some useful ideas, or ask here - it's not complex.

One potential issue is if some records are shorter than others, producing data like 384;38;"Foo";;;;;;
so you'll have to evaluate if that requires handling such as with IF logic.

You may also want to experiment with DisplayAlerts to enhance the pleasure of your experience

 

[Felix Atagong]

Thanks for your info, especially for the VBA workaround to make semicolon defined CSV files. That'll come in handy!

However the Microsoft Support page is wrong: on all my 'Belgian' PC's, except one, the VBA uses the regional settings (semicolon) as a delimiter, even in VBA. I guess that Microsoft wants to say: we do not know why CSV-VBA sometimes takes a comma and sometimes a semicolon. Our helpdesk has given up trying to find the reason...

 

[kakzhetak]

Complete

Sub CreateCSV()

    Dim rCell As Range
    Dim rRow As Range
    Dim sOutput As String
    Dim sFname As String, lFnum As Long
        
    'Open a text file to write
    sFname = "C:\MyCsv.csv"
    lFnum = FreeFile
    
    Open sFname For Output As lFnum
    'Loop through the rows'
        For Each rRow In ActiveSheet.UsedRange.Rows
        'Loop through the cells in the rows'
        For Each rCell In rRow.Cells
            sOutput = sOutput & rCell.Value & ";"
        Next rCell
         'remove the last comma'
        sOutput = Left(sOutput, Len(sOutput) - 1)
        
        'write to the file and reinitialize the variables'
        Print #lFnum, sOutput
        sOutput = ""
     Next rRow
    
    'Close the file'
    Close lFnum
    
End Sub

Hope smb enjoy it)

 

[tt388]

kakzhetak

I am also looking for a script to do semicolon delimited. I try yours and it is pretty good. However, the number of columns in the my excel file is not consistent. for example, using your code, the CSV file generated is:

1;2;3;4;5
1;2;;;
1;2;3;;

Can you modify the code so the semi-colon stops after the last cell of the row:
1;2;3;4;5
1;2
1;2;3

Thanks.

 

[Martygofast]

I am having the same issue, is there anyone that can solve that issue

1;2;3;4;5
1;2;3;;
1;;;;

to

1;2;3;4;5
1;2;3
1

 

[kylskapet]

I have used kakzhetak code and it works fine for me, but is there anyone that knows a way to modify it so that i can choose in which folder the csv should be saved?

/dave

 

[rubeni]

Hello,

I want to select particular columns and rows from my excel sheet and then convert in to CSV.

I do not want to separate by comma delimited BUT by |.

For example : |Name|LastName|Address|Contact|City

Thanks

 

[Spaceman]

Solve the empty cell problem by adding something to:

For Each rCell In rRow.Cells
      sOutput = sOutput & rCell.Value & ";"
Next rCell

Namely

For Each rCell In rRow.Cells
     If Not Isempty(rCell) Then
     sOutput = sOutput & rCell.Value & ";"
     End If 
Next rCell