Page 1 of 2 12 LastLast
Results 1 to 10 of 13

xlCSV = comma, Save As CSV = semicolon delimited?

This is a discussion on xlCSV = comma, Save As CSV = semicolon delimited? within the Excel Questions forums, part of the Question Forums category; I have daily Excel files to convert in 'European' CSV format, this is delimited with a semicolon. If I open ...

  1. #1
    Board Regular Felix Atagong's Avatar
    Join Date
    Jun 2003
    Location
    Brussels
    Posts
    357

    Default xlCSV = comma, Save As CSV = semicolon delimited?

    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,
    LycosMailWhiteList=IF(RIGHT(e-mail-address, 11)="MrExcel.com", LycosMailInbox, DeleteFromMailServer)
    Felix Atagong's Unfinished Projects

  2. #2
    Board Regular Felix Atagong's Avatar
    Join Date
    Jun 2003
    Location
    Brussels
    Posts
    357

    Default

    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!
    Felix Atagong,
    LycosMailWhiteList=IF(RIGHT(e-mail-address, 11)="MrExcel.com", LycosMailInbox, DeleteFromMailServer)
    Felix Atagong's Unfinished Projects

  3. #3
    Board Regular Gates Is Antichrist's Avatar
    Join Date
    Aug 2002
    Location
    Earth (on working assignment from Hell)
    Posts
    1,961

    Default

    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...;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/2...-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
    Outlook 2007: Try to find undo and redo now in the menus and icons. I'm not kidding. Microsoft, you CLUELESS ***TARDS.

  4. #4
    Board Regular Felix Atagong's Avatar
    Join Date
    Jun 2003
    Location
    Brussels
    Posts
    357

    Default

    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...
    Felix Atagong,
    LycosMailWhiteList=IF(RIGHT(e-mail-address, 11)="MrExcel.com", LycosMailInbox, DeleteFromMailServer)
    Felix Atagong's Unfinished Projects

  5. #5
    New Member
    Join Date
    Oct 2008
    Posts
    1

    Default Re: xlCSV = comma, Save As CSV = semicolon delimited?

    Complete
    Code:
    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)

  6. #6
    New Member
    Join Date
    Apr 2006
    Posts
    9

    Default Re: xlCSV = comma, Save As CSV = semicolon delimited?

    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.

  7. #7
    New Member Martygofast's Avatar
    Join Date
    May 2009
    Location
    Rotterdam
    Posts
    11

    Default Re: xlCSV = comma, Save As CSV = semicolon delimited?

    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

  8. #8
    New Member
    Join Date
    Mar 2010
    Posts
    21

    Default Re: xlCSV = comma, Save As CSV = semicolon delimited?

    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

  9. #9
    New Member
    Join Date
    Aug 2010
    Posts
    1

    Default Re: xlCSV = comma, Save As CSV = semicolon delimited?

    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

  10. #10
    New Member
    Join Date
    Sep 2010
    Posts
    4

    Default Re: xlCSV = comma, Save As CSV = semicolon delimited?

    Solve the empty cell problem by adding something to:

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

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

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com