-
Jun 29th, 2022, 09:32 AM
#1
Thread Starter
New Member
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.
-
Jun 29th, 2022, 09:45 AM
#2
Thread Starter
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|