Results 1 to 2 of 2

Thread: Exporting UTF-8 CSV with double quotes from Excel

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2022
    Posts
    2

    Exporting UTF-8 CSV with double quotes from Excel

    According to Microsoft you can just use their Visual Basic macro to export columns from a table to a CSV with double quotes (https://docs.microsoft.com/en-US/off...th-comma-quote). The following code words fine for me in MacOS:

    Code:
       Sub QuoteCommaExport()
           ' Dimension all variables.
           Dim DestFile As String
           Dim FileNum As Integer
           Dim ColumnCount As Long
           Dim RowCount As Long
        
           ' Prompt user for destination file name.
           DestFile = InputBox("Enter the destination filename" _
              & Chr(10) & "(with complete path):", "Quote-Comma Exporter")
        
           ' Obtain next free file handle number.
           FileNum = FreeFile()
        
          ' Turn error checking off.
           On Error Resume Next
        
           ' Attempt to open destination file for output.
           Open DestFile For Output As #FileNum
        
           ' If an error occurs report it and end.
           If Err <> 0 Then
              MsgBox "Cannot open filename " & DestFile
              End
           End If
        
           ' Turn error checking on.
           On Error GoTo 0
        
           ' Loop for each row in selection.
           For RowCount = 1 To Selection.Rows.Count
        
           ' Loop for each column in selection.
              For ColumnCount = 1 To Selection.Columns.Count
        
                ' Write current cell's text to file with quotation marks.
                 Print #FileNum, """" & Selection.Cells(RowCount, _
                    ColumnCount).Text & """";
        
                 ' Check if cell is in last column.
                 If ColumnCount = Selection.Columns.Count Then
                    ' If so, then write a blank line.
                    Print #FileNum,
                 Else
                    ' Otherwise, write a comma.
                    Print #FileNum, ",";
                 End If
              ' Start next iteration of ColumnCount loop.
              Next ColumnCount
           ' Start next iteration of RowCount loop.
           Next RowCount
        
           ' Close destination file.
           Close #FileNum
        End Sub
    However the final outputted file does not seem to use the UTF-8 charset.

    How can the above Visual Basic be edited so that the resultant CSV is encoded using UTF-8?

    At the moment, the outputted CSV file has an unusual encoding. Examining the CSV in a HEX editor shows that a character like `ä` is stored as a single byte (`8A`). When this CSV is opened in Excel it correctly shows `ä`, but in everything else it shows `�`.

    I don't know what character encoding Excel is using, as this character should be typically stored as `E4` when using a single byte, or `C3 A4` in UTF-8.
    Last edited by JohnnyW; Jun 29th, 2022 at 09:45 AM.

  2. #2

    Thread Starter
    New Member
    Join Date
    Jun 2022
    Posts
    2

    Re: Exporting UTF-8 CSV with double quotes from Excel

    Oops. I didn't need to write this reply: Please ignore this msg Everything is written above.

Posting Permissions

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



Click Here to Expand Forum to Full Width