I use ODBC links to text files on a regular basis and often have to join files coming from different systems. Recently I had to join a Unicode file with an ANSI encoded one. Simple, you’d think, as the scant documentation on the text files ODBC driver does mention dealing with Unicode. However, the Define Text Format dialog used to configure the ODBC text driver (results stored in schema.ini) has only two radio buttons: ANSI and OEM. I couldn’t get either to work with the Unicode file and so had to convert it to ANSI format before I could use it. A real pain. Then a eureka moment. Manually edit schema.ini for the Unicode file entry to include the line CharacterSet=UNICODE. It works!
After spending ages on this I was frustrated to find a document called "Initializing the Text Data Source Driver" in information relating to Microsoft Office Access 2003. The discussion appears to be about the Microsoft Jet database engine but it confirms that CharacterSet can also contain UNICODE or the code page number of a specific character set (e.g. 1232)
The relevant document is at: