IMPORT CSV OR ANY DELIMITED TEXT FILE WITH UNICODE ENCODINGS IN EXCEL USING VBA

This post demonstrates how to import CSV or any delimited text file with Unicode encoded in Excel using VBA code.

By default, text files are encoded in ANSI representation on WINDOWS platform.

When you try to import the ANSI encoded text files into excel using VBA, you may not see problems with the data appearing in the excel worksheet. But there is a problem when importing a text file other than ANSI encoding.

  The Possible Encodings of a Text File – 

 The following are the available character encoding representations of a text file on WINDOWS platform.

• ANSI (default encoding representation)
• Unicode
• Unicode big endian
• UTF-8

How to know the encoding of a text file?

1. Open a text file with notepad editor.

 2. Select File menu and click on Save As

 3. Look at the Encoding at the bottom, which tells you the current encoding of a text file.

 4. Click on Encoding drop-down list, to see the supported encoding representations of a text file on your machine.

What are these encodings?

To understand more about these encodings, please check this link – Byte Order Mark (BOM)

What happens when Unicode encoded text is imported into Excel –

The below sample text file is used for testing. The copy of this file is saved with all possible encodings.

  4 Rows

 3 Columns

Delimited by ‘,‘ (Comma) 





The following is the output screen when each encoded text file is imported into Excel. When the import button is clicked, the data will be imported from the active cell in a worksheet.
 
If you look at the output screen, the every first cell of each encoding contains a garbage value.

Reason –

Byte Order Mark (or BOM) is a signal that tells the computer how the bytes are ordered in a Unicode document. Because Unicode can be used in the formats of 8, 16 and 32 bits – it is important for the computer to understand which encoding has been used in the Unicode document. BOM tells exactly the same to the computer. That is why we see the garbage values.

Representation of BOM by encoding –

This table illustrates how BOMs are represented as byte sequences and how they might appear in a text editor that is interpreting each byte as a legacy encoding (CP1252).

Encoding	Representation (hexadecimal)	Representation (decimal)	Bytes as CP1252characters
UTF-8[t 1]	EF BB BF	239 187 191	
UTF-16(BE)	FE FF	254 255	þÿ
UTF-16(LE)	FF FE	255 254	ÿþ

As per these representations, we can see the fixed characters for each Unicode encoding when the text file is imported.

How to Resolve –

This can be resolved by replacing the Unicode representation characters with an empty string.

Import Button Code –

1 2 3

Sub DoTheImport() ImportTextFile FName:="D:\data.txt", Sep:="," End Sub

ImportFile is the sub-routine which imports the data. Change the sep:= as per your requirement. In this example comma is the separator.

ImportFile Code –

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56

Public Sub ImportTextFile(FName As String, Sep As String) Dim RowNdx As Long Dim ColNdx As Integer Dim TempVal As Variant Dim WholeLine As String Dim Pos As Integer Dim NextPos As Integer Dim SaveColNdx As Integer 'disable screen updates Application.ScreenUpdating = False 'error handling On Error GoTo EndMacro 'Importing data starts from the selected cell in a worksheet SaveColNdx = ActiveCell.Column RowNdx = ActiveCell.Row 'open the file in read mode Open FName For Input Access Read As #1 'Read the file until End of Line While Not EOF(1) 'read line by line Line Input #1, WholeLine WholeLine = Replace(WholeLine, "", "", vbTextCompare) 'UTF-8 WholeLine = Replace(WholeLine, "ÿþ", "", vbTextCompare) 'UTF-16 Unicode little endian WholeLine = Replace(WholeLine, "þÿ", "", vbTextCompare) 'UTF-16 Unicode big endian 'checking if the line is empty If Right(WholeLine, 1) <> Sep Then WholeLine = WholeLine & Sep End If ColNdx = SaveColNdx Pos = 1 NextPos = InStr(Pos, WholeLine, Sep) 'finding each column data While NextPos >= 1 TempVal = Mid(WholeLine, Pos, NextPos - Pos) Cells(RowNdx, ColNdx).Value = TempVal Pos = NextPos + 1 ColNdx = ColNdx + 1 NextPos = InStr(Pos, WholeLine, Sep) Wend RowNdx = RowNdx + 1 Wend Close #1 Exit Sub EndMacro: Application.ScreenUpdating = True MsgBox Err.Description, vbOKOnly, "Error" Close #1 End Sub

Summary –

This way you can import any delimited text file with Unicode encodings. Make sure that the text file is not exceeding the maximum number of rows of excel worksheet.

Please share your thoughts on this post in the comments section.

Karthik Byggari

Author & Editor

Computer Science graduate, Techie, Founder of logicallyproven, Love to Share and Read About pprogramming related things.