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


eOne is now compatible with Microsoft Dynamics GP October 2019 Release
Change in eOne Product Registration Process
October 2019 Releases of Extender, SmartList Builder, SmartView, SmartPost, and Flexicoder are Available Today
Tech Tuesday: Creating Calculations in SmartList / SmartView
SmartConnect Office Hours for Friday, October 4th, 2019

POPULAR POSTS


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

CATEGORIES

ARCHIVES

open all | close all

TAGS

Business Central CRM Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News eOne Spotlight Events Excel Excel Report Builder Extender Extender Enterprise Flexicoder GP GPUG integration Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Office Relationships partners Popdock release Reporting SalesForce SalesForce.com scripting SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Office Hours SmartList SmartList Builder SmartList Designer SmartView SQL Support Tech Tuesday Templates training Zendesk

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.