Friday, May 25, 2012

Microsoft Text Driver on Windows 2008 R2

I had to set up Microsoft Text Driver on Windows 2008 R2 64-bit machine and was constantly getting errors. Here's what I had to go through to get this to work:

There are 2 different versions of the ODBC drivers on Windows 2008 R2 - 64 bit version and a 32-bit version. When you open the ODBC Datasource Administrator Tool from "Administrative Tools> Datasources (ODBC)", its the 64 bit version that gets opened. I tried to create a DSN using a text driver but did not find the text driver in the list of drivers. So initially I thought that the text drivers were not installed. But when I searched for the msjet40.dll file I did find it in the c:\Windows\SysWOW64 directory. Refer
So this indicated to me that a 32-bit version of the driver was available. This KB article from Microsoft confirmed this:

So I found the 32-bit version of the ODBC Administrator. This is located at: C:\Windows\SysWOW64\Odbcad32.exe

Once I open this file, it opened the 32bit ODBC Administrator interface and it did have the Microsoft text driver listed in there. So I added my datasource and changed my file extensions to use only *.csv.

Now that the datasource was created, I tried to use it. But it threw the following error:
[ODBC Text Driver] Invalid setting in Excel key of the Engines section of the Windows Registry. I was stumped. After much searching on the Internet, nothing seemed to work.
So as a last resort I deleted the datasource and added a brand new one. This time, I left the defaults and it just worked. So I tried it one more time and created the datasource with the file extensions set to *.csv only. it again did not work and threw the same error. Now when I went to edit the file extensions, I saw that the *.csv had been mangled to some weird characters. *.%^767 or something like that. I tried different file extensions but none worked.
So finally to get this to work, I had to leave it to use *.* as the list of file extensions.

No comments: