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.
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?
What happens when Unicode encoded text is imported into Excel –
Delimited by ‘,‘ (Comma)
The following is the output screen when each encoded text file is imported into Excel. When the
importbutton 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.
Representation of BOM by encoding –
|Encoding||Representation (hexadecimal)||Representation (decimal)||Bytes as CP1252characters|
How to Resolve –
Import Button Code –
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 –
Please share your thoughts on this post in the comments section.