Tech Tuesday: Issues to be aware of when creating maps that use text ODBC drivers

I get a lot of calls from people saying that SmartConnect isn’t working right. It’s either not reading their data correctly, or it’s not reading all the columns from their source file, or it’s actually saying that the source file is completely blank, when in fact it does have data in it. Most of these issues are due to limitations in the Microsoft text ODBC drivers. There are certain conditions that need to be met when reading files with ODBC text drivers.
______________________________________________________________________________

The file needs to have a .txt or a .csv extension on it. Even if the file internally is a tab or csv delimited file, alternate file extensions do not work. For example, extensions like .dat or .db, or .log will not get read by the text ODBC driver.

Multiple file extensions do not work. So for example, if you have a file named “mysourcedata.old.db.csv.log.txt” will not work. Even though the last extension is .txt, having multiple file extensions on the file will cause it not to be read. Also any additional periods in a file name get treated as a file extension. For example if the file is named “Inv. Items for import.txt” that extra period in the file name is going to cause the file to not be read.

The maximum file length of a text file name cannot be greater than 64 characters. Files with names longer than 64 characters long will get read as empty files.

The maximum length of individual column names cannot be longer than 64 characters.

The maximum total length of the pathname plus the file name cannot exceed 255 characters. So for example if your pathname to the files is c:source filesmy source dataaccountingcompany name2017AugustDaily EntriesGLFiles to get imported this would use up 106 characters of the allowable 255 characters.

The max number of columns that can get read by the ODBC text driver is 255 columns. So even if you have 400 columns in your source file, only the first 255 will actually get read. I see this most of the time with Concur import files. They will supply you with a CSV file with over 400 columns in it, that cannot be read by the standard Microsoft ODBC text driver. You need to remove unneeded columns before it will be able to be read.

Text files are single user. If a user has a text file opened, and another user attempts to read the file using the Text ODBC driver, it’s going to read the file as blank. The Text ODBC driver need to have exclusive access to the file in order for it to be read.

Once these conditions are met, you should be good to go!