how to get encoding type of a file csv in VBA(Exel)

Question

0.00/5 (No votes)

See more:

I have some file CSV and I want to get their encoding type using VBA in Exel.

but I can not do this.

Could you help me resolve this problem?

thanks for advance

Posted 13-Apr-11 1:36am

ngthtra

Updated 14-Apr-11 23:14pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

E.F. Nijboer · Answer 1 · 2011-04-14T05:34:00

Solution 1

For VB.NET you could use the detectEncodingFromByteOrderMarks parameter of the StreamReader class to detect this.
http://msdn.microsoft.com/en-US/library/akzyzwh9%28v=VS.80%29.aspx[^]

Unfortunately this isn't available for VBA/VB6. Possibly you could detect it yourself by reading the first bytes from it and use that to determine the encoding.
http://codesnipers.com/?q=node/68[^]

For reading you could use the FileSystemObject that has support for default, ASCII and unicode.
http://msdn.microsoft.com/en-us/library/314cz14s%28v=VS.85%29.aspx[^]

Good luck!

Posted 14-Apr-11 5:34am

E.F. Nijboer

Comments

ngthtra 15-Apr-11 3:22am

Thanks your link. But I can not find way that help me resolve my problem.
can you tell me how to use streamReader on VBA(code in Exel)?
because If use streamReader in VB.NET I can get encoding type of CSV file.

E.F. Nijboer 15-Apr-11 4:31am

You use the FileSystemObject for that. This returns a TextStream that you can then use. I actually don't know if VBA has any support for other encodings. However, I have two other links that might interest you:
http://www.nonhostile.com/howto-convert-byte-array-utf8-string-vb6.asp
http://www.tek-tips.com/viewthread.cfm?qid=1392917

ngthtra 15-Apr-11 5:31am

they don't resolve this problem, I need get encoding while they convert.and now I try to filesystemobject, I not sure that it is good

E.F. Nijboer 15-Apr-11 7:41am

I think the second link (codesnipers) can help you with this. You simply read the text file into a byte array (although I think you don't need the complete file for this) and check for a signature. If no signature is found then assume ansi.
http://www.nonhostile.com/howto-read-write-byte-arrays-files-vb6.asp

To test if the file is unicode you can also have a look at this:
http://www.apihosting.com.ar/manuales/es/online/vbapi/apidetail.asp?api_id=471

ngthtra 18-Apr-11 2:59am

this way is applyed for unicode,I am using EUC and Shift_JIS.how to determine what file is EUC and What file is Shift_JIS?

E.F. Nijboer 18-Apr-11 6:24am

You could do some searches on that. I found some info about it but it isn't that easy and certainly not in vba.
General info: http://www.mozilla.org/projects/intl/UniversalCharsetDetection.html
c example: http://www.lemoda.net/encodings/detect-shift-jis/index.html

You could also consider creating a form that you show users where they can enter/select the filename and select the encoding themselves from a combobox. This way you make the user responsible for that which could be an acceptable solution.

ngthtra 4-May-11 6:44am

I searched on internet but there are anybody get endcoding type for all type successfully.
Special japanese type

E.F. Nijboer 4-May-11 7:22am

You mean you did find something? Or couldn't find anyone that successfully can detect Japanese encoding? If you couldn't find anything you might want to consider the option in the previous reply and ask users to specify the correct encoding themselves.