Skip to content
+1-888-319-3663

EONE BLOG

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.

  1. 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.
  2. 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.
  3. 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.
  4. The maximum length of individual column names cannot be longer than 64 characters.
  5. 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 files\my source data\accounting\company name\2017\August\Daily Entries\GL\Files to get imported\ this would use up 106 characters of the allowable 255 characters.
  6. 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.
  7. 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!

Leave a Comment





RECENT POSTS


2021 Q4 Promotions
Connect with us at Directions EMEA 2021 in Milan!
eOne at Summit North America 2021
Employee Spotlight: Megan Tripp, Business Development Representative
eOne is now compatible with Microsoft Dynamics GP October 2021 Release

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder

CATEGORIES

TAGS

Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships partners Popdock release Reporting SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.