Open .csv files and show Spanish characters UTF-8 using VBA

[Txusmah]

Hello,

I have a .csv file with data that contains spanish characters (á, é, ñ...). If opened in Notepad++ I can see the characters correctly, but when I open it in Excel I get the typical strange characters (Fern~ºndez, instead of Fernández).

I need to open several of this files using VBA and do some changes, so I would like to know if there is any code to do this:
- Open a .csv in UTF-8 so I can get the proper spanish characters
- Save it as regular .xlsx file.

Thanks a lot.

Txus

 

[John_w]

Try this macro. You must edit the code where indicated to specify the full name of the .csv file. It saves the .xlsx file in the same folder and with the same file name.

#If VBA7 Then
    'Maps a character string to a UTF-16 (wide character) string
    Private Declare PtrSafe Function MultiByteToWideChar Lib "kernel32" _
        (ByVal CodePage As Long, ByVal dwFlags As Long, ByVal lpMultiByteStr As LongPtr, ByVal cchMultiByte As Long, ByVal lpWideCharStr As LongPtr, ByVal cchWideChar As Long) As Long
#Else
    Private Declare Function MultiByteToWideChar Lib "kernel32" _
        (ByVal CodePage As Long, ByVal dwFlags As Long, ByVal lpMultiByteStr As Long, ByVal cchMultiByte As Long, ByVal lpWideCharStr As Long, ByVal cchWideChar As Long) As Long
#End If

'UTF-8 code page
Private Const CP_UTF8 = 65001


Public Sub Convert_UTF8_CSV_File_To_Workbook()

    Dim csvFile As String
    Dim csvWorkbook As Workbook
    Dim fileNum As Integer
    Dim UTF8csvLine As String, VBAstring As String
    Dim r As Long, csvParts As Variant
    
    csvFile = "C:\path\to\UTF8 file.csv"    'CHANGE THIS
    
    Application.ScreenUpdating = False
    
    Set csvWorkbook = Workbooks.Add(xlWBATWorksheet)
    r = 1
    
    fileNum = FreeFile
    Open csvFile For Input As #fileNum
    While Not EOF(fileNum)
        Line Input #fileNum, UTF8csvLine
        VBAstring = UTF8StringToVBAString(UTF8csvLine)
        csvParts = Split(VBAstring, ",")
        csvWorkbook.Worksheets(1).Cells(r, 1).Resize(, UBound(csvParts) + 1).Value = csvParts
        r = r + 1
    Wend
    Close #fileNum
    
    Application.DisplayAlerts = False  'suppress warning if .csv file already exists - the file is overwritten
    csvWorkbook.SaveAs Filename:=Replace(csvFile, ".csv", ".xlsx", Compare:=vbTextCompare), FileFormat:=xlOpenXMLWorkbook
    csvWorkbook.Close False
    Application.DisplayAlerts = True
    
    Application.ScreenUpdating = False
    
End Sub


'Use MultiByteToWideChar API function to convert a string of UTF-8 characters to a VBA (Unicode) string
Private Function UTF8StringToVBAString(ByRef UTF8string As String) As String
    
    Dim UTF8bytes() As Byte
    Dim bufferSize As Long
    
    UTF8bytes = StrConv(UTF8string, vbFromUnicode)
    
    'Get required size of output string
    bufferSize = MultiByteToWideChar(CP_UTF8, 0, VarPtr(UTF8bytes(0)), UBound(UTF8bytes) + 1, 0, 0)
    
    'Allocate output string
    UTF8StringToVBAString = String$(bufferSize, 0)
    
    'Convert UTF8 bytes to Unicode output string
    MultiByteToWideChar CP_UTF8, 0, VarPtr(UTF8bytes(0)), UBound(UTF8bytes) + 1, StrPtr(UTF8StringToVBAString), bufferSize

End Function

 

[Txusmah]

Thanks a lot for your response. Unfortunately, it does not work.

The result file has only 1 row and in each cell I get a full row with all cells separated by ";"

 

[John_w]

The result file has only 1 row and in each cell I get a full row with all cells separated by ";"

But are the Spanish characters correct?

csv means comma-separated values, hence the "," in csvParts = Split(VBAstring, ","). Change that to ";".

What character(s) are the line endings? Line Input works with Chr(13) or Chr(13) + Chr(10).

Can you upload the .csv file and post the link here?