Loading in UTF-8, not ASCII

Hi there,

I have a very large text file (some 400,000 lines of text) that I'm loading into memory with VBA, I then use VBA to analyse and sort the data, before putting it into Excel. I use the folling code to load the text file into memory...

iFile = Freefile
Open sFile For Input As #iFile
LoadTextFile = Input$(LOF(iFile), iFile)

This takes a few seconds to run and has been working fine, up until some special characters have appeared in the text file, and so when I load in the file they get messed up. So for example "Sion" becomes "SiA'n" (missing some accents there). It turns out that the program that creates the text file can only output in UTF-8 encoding, and seemingly VBA only really deals with ASCII, hence the text getting screwed up. I have tried the following code to allow me to load in UTF-8, but it is painfully slow to the extent I cannot use it for my application. So my question is, does anyone have any other options I can try that allows me to load in UTF-8 encoded text, but that's also fast?

Dim objStream As Object
Dim strData As String
Set objStream = CreateObject("ADODB.Stream")
objStream.Charset = "utf-8"
objStream.Open
objStream.LoadFromFile (sFile)
LoadTextFile = objStream.ReadText()

Chris

Thanks for your reply. At this stage I'd rather not go down the road of installing anything, it's on a network with many different users using the file so that would need to be a last resort really.

Hi !

As Excel can read txt file encoded in utf-8, just see within the import assistant …

All needs to be done entirely within VBA as it's an automated process.

No problemo !

As everyone can try manually and activate the Macro Recorder …

That certainly gets me the data into Excel, but it's not ideal for getting it into memory in one long string. I use Shell() to access the program that delivers me the UTF-8 text file, but I'm thinking I can use Windows powershell to convert the encoding, which you can do via Shell(). It's not the fastest of options, takes around 5 to 10 seconds to do the conversion depending on the file size, but then that allows me to load in the text file as before. That could be the best way, I shall do some testing.

Yes but within a temp worksheet you can apply a filter, a sort, … and as Excel internal features it's pretty fast in VBA.

The other way I use too is ADODB.Stream …

Ok I'm going to do a comparison test between Workbook.Open to load in UTF-8, and the powershell conversion, which I then need to load in. Whichever comes in fastest I shall use!

ADODB.Stream is far too slow for the size of text files I'm dealing with sadly.

Thanks!

Just to update, I've gone down the powershell route. The files I'm working with are actually XML files, but I need to read them in as text, Workbook.OpenText doesn't like that, and just tries to load the file as XML. So the faff of having to rename the file to .txt, load in as text, then get the text into memory from the worksheet and use Join() to decipher the XML was too long winded. Using powershell you can use the [io.file]::ReadAsText command, and then WriteAsText to output as ASCII, this is pretty much instant (with ~20MB files anyway), and then I just have to load this file in as normal. As this process has almost no overhead it's the obvious choice for me.