Microsoft.Jet.OLEDB.4.0 Converting Characters

Question

Solution 1

Finally! Thanks to @HABJAN I was able to get to the resolution which is as simple as setting the CharaterSet in the Extended Properties of the connection string. For my situation it was UTF-8...commonly used by default in PHPMyAdmin which is where my data was retrieved from.

Resulting working connection string:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"{0}\";Extended Properties=\"text;HDR=Yes;FMT=Delimited;CharacterSet=65001;\""

Key is CharacterSet=65001 (Code Page Identifier for UTF-8) which might have been obvious to some collation savvy individuals but I've somehow managed to avoid these issues over the years and never come across it in this respect.

I was also able to get HABJAN's solution to work when also following the documentation found @ http://msdn.microsoft.com/en-us/library/ms709353%28v=vs.85%29.aspx and setting the CharacterSet to the same as above.

For my situation, this is the better method as it is a simpler/more maintainable solution, but +1 to HABJAN for helping me get there!

Thanks

Solution 2

You can create schema.ini file and play with format and CharacterSet properties.

Take a look at this sample: How to read data from Unicode formatted text file and import to Data Table using .Net

And here is another sample that will show you how to read csv file with schema.ini: Importing CSV file into Database with Schema.ini