Catching a superscript in a .csv file header?

[jxb]

I have a macro to post process data (in a csv format) and import it in a 1/3 party tool. Works fine

I have however just got an issue because the data header contains a (string) description a2/b in that the 2 is a (real) superscript (in the csv file) but my macro search for a2/b i.e. no superscript to do something with the column. This was the original format I worked with! Having done some “debugging” I found that the macro seems to recognise the format as a?/b where ? appears as a black losange. What is this format?

More importantly now that I know I could end up with this issue is there a way of catching this format “on the fly”. I could search on both?

Granted that I could have a small Check function to replace the culprit string but I still need to know how to find it

Thanks
Regards

 

[GWteB]

I found that the macro seems to recognise the format as a?/b where ? appears as a black losange. What is this format?

It isn't a format. It's just displayed like that. The VBE cannot handle special characters like ² so these are displayed as a question mark ?

The approach is dependent on the binary file format, since a .csv or .txt file can have different appearances on disk. If the file is "pure" ANSI each character consists of one byte, in UTF-8 or UTF-16 (both big endian & little endian) special characters are represented by two bytes. You could try replacing the special character within Excel after importing. Perhaps it would be better to read the contents of the file beforehand, replace all occurences of ² with 2 and write it back again.

_ file format _ : ² character

ANSI      : 178
UTF-8     : 194 & 178
UTF-16 LE : 178 & 0
UTF-16 BE :   0 & 178

 

[jxb]

It isn't a format. It's just displayed like that. The VBE cannot handle special characters like ² so these are displayed as a question mark ?

The approach is dependent on the binary file format, since a .csv or .txt file can have different appearances on disk. If the file is "pure" ANSI each character consists of one byte, in UTF-8 or UTF-16 (both big endian & little endian) special characters are represented by two bytes. You could try replacing the special character within Excel after importing. Perhaps it would be better to read the contents of the file beforehand, replace all occurences of ² with 2 and write it back again.

_ file format _ : ² character

ANSI      : 178
UTF-8     : 194 & 178
UTF-16 LE : 178 & 0
UTF-16 BE :   0 & 178

Thanks for the explanation. I had a look and indeed changing beforehand the "faulty" header appear to be the easiest apprpoach I already have a macro to do a couple things and a 1st rough test using

Selection.Replace

seems to do the job. I just need to add a couple of lines in my existing loop

Thanks again

 

[GWteB]

You are welcome and thanks for the feedback.