Due to a recent Microsoft Windows/Office update around September 2, 2020; SmartConnect is unable to properly access text files that have 255 columns. The delimiter of the file does not matter as the issue has occurred for comma, tab, and pipe delimited files.
The issue presents itself in the following scenarios:
- Receiving the error “Too many fields defined” creating the file schema with a Text File data source
- A Folder data source finding no records in a source file and it is moved to the success folder
- An ODBC Data Source Text returns “Too many fields defined” when opening a map
NOTE: Prior to making any changes to your system with steps from this document, it is recommended to make a SQL backup of your SmartConnect database.
Resolution
The main workaround to resolving the issue is for us to switch the integrations to using the Microsoft Access Text Driver versus the Microsoft ODBC Text Driver. If it is not already installed, the Microsoft Access Text Driver can be found by downloading and installing the 32-bit Microsoft Access Database Engine 2010 redistributable that can be downloaded from here.
Steps for installing are here.
Once the drivers are installed, we will need to modify the current integration to use the Microsoft Access Text Driver.
After changing any map data source validate the mappings are correct prior to running the integration.
Too Many Fields Defined Error
When receiving this error, we only need to change the data source to an ODBC Connection
I receive “ERROR [HY001] [Microsoft] [ODBC Text Driver] Too many fields defined” error when opening a map or trying to define a schema file with the associated Text document.
If we are using the Data Source Type of Bulk Data Load and Data Source of Text File, as below, we will need to switch the Data Source to ODBC Connection.
Instructions for switching the Data Source to use ODBC Connection are here.
Folder Data Source Not Reading Source File
System: I have a folder data source that processing zero records however the file, with data, is moving directly to the Success Folder.
For this issue there are two steps:
- Switch from using a Folder Data Source to an ODBC Connection following the steps Here.
- After changing the Data Source from Folder Data Source to ODBC Connection, we need to create our Move File tasks to this map as they are no longer handled by the process.
Move File Task
To move the source file from the current location to the success or failure folder, we need to create a Script Task on “Tasks the run if the map succeeds” and “Tasks that run if the map fails”.
Click on the Tasks Tab on the map
Go to the Map Post Tasks
Right click on the “Tasks that run if the map fails”, choose New Task, Choose Run Script
Provide a name for task and enter the following script to move the file to the failure folder.
Be sure to update the correct file names and folders.
Dim dateTimeInfo as DateTime = DateTime.Now
Dim dateString as String = dateTimeInfo.ToString("_yyyy-MM-dd_HH-mm-ss")
Dim sourcefile as string = "C:UsersPublicDocumentsExpenseSourcenew_APGL_Extract_V_3_04.txt"
Dim destfile as string = "C:UsersPublicDocumentsExpenseFailurenew_APGL_Extract_V_3_04" + dateString + ".txt"
File.Move(sourcefile,destfile)
return true
Repeat the above steps for creating a Task on Map Success
Provide a name for task and enter the following script to move the file to the failure folder.
Be sure to update the correct file names and folders.
Dim dateTimeInfo as DateTime = DateTime.Now
Dim dateString as String = dateTimeInfo.ToString("_yyyy-MM-dd_HH-mm-ss")
Dim sourcefile as string = "C:UsersPublicDocumentsExpenseSourcenew_APGL_Extract_V_3_04.txt"
Dim destfile as string = "C:UsersPublicDocumentsExpenseSuccessnew_APGL_Extract_V_3_04" + dateString + ".txt"
File.Move(sourcefile,destfile)
return true
Your integration should now work as before.
Creating an ODBC Connection
NOTE: Before changing any map, use the Duplicate Map option from the Map Menu or make a SQL backup of your SmartConnect database.
To fix our issue with the Microsoft ODBC Text Driver failing, we need to change the Data Source to ODBC Connection.
We have two options at this point. We can select a Connection Type to the file/folder using the Connection Type drop down or click on the Ellipsis button for the Connection String and select the FileFolder to be used.
Connection String
We can define the connection string by clicking on the ellipsis and selecting TextFile from the list of connections.
The full steps are explained in this article.
ODBC Text Setup
With the ODBC Text Setup window open, uncheck the Use Current Directory option.
Click on the Directory Button
Browse to the folder containing the source file
Click on the Options Button
Click Define Format …
With the Define Text Format window open, click on the Source File and click Guess
If the stars align, we will have the right columns and names.
NOTE: You will need to verify the data type for each column is the same as it was using the original data source.
You may receive this error, which means we will need to change a setting in the SmartConnect database. The steps for that are here.
If we receive the following error proceed to the step of creating a User or System DSN and changing the map to use this option.
In addition to using the User or System DSN, we will want to reset our SmartConnect Connection String Template back to its original value.
Creating a Machine DSN
The steps for creating a connection to a Machine DSN are documented in this article.
Select Windows+R and enter C:WINDOWSSysWOW64odbcad32.exe
Depending the type of DSN you want to create, User would be for only the current logged in user and System would be for any user logging into this machine, Click on the User DSN or System DSN.
Click the Add Button
Select the Microsoft Access Text Driver
Click Finish
Follow the steps for setting up an ODBC Text Setup
When going back to the SmartConnect Map and you choose the ODBC Connection type select “Select a DSN”.
Pick the Machine Data Source tab and select the ODBC Text DSN that was just created.
NOTE: Validate the mappings on your destination nodes.
Updating SmartConnect Connection String Template
NOTE: The following steps will require a restart of SmartConnect and the SmartConnect Windows Service to take effect.
Using SQL Management Studio, execute the following SQL to update the driver we are going to use for the ODBC Connection Text Files.
UPDATE SmartConnect..ConnectionStringTemplate SET ConnectionString = ‘Driver={Microsoft Access Text Driver {*.txt; *.csv};Dbq=C:;Extensions=asc,csv,tab,txt; ‘ WHERE ConnectionStringTemplateId = 18
Resetting SmartConnect Connection String Template
NOTE: The following steps will require a restart of SmartConnect and the SmartConnect Windows Service to take effect.
Using SQL Management Studio, execute the following SQL to update the driver we are going to use for the ODBC Connection Text Files.
UPDATE SmartConnect..ConnectionStringTemplate SET ConnectionString = ‘Driver={Microsoft Text Driver {*.txt; *.csv};Dbq=C:;Extensions=asc,csv,tab,txt; ‘ WHERE ConnectionStringTemplateId = 18
After this step, relaunch SmartConnect.
Microsoft Access Database Engine 2010 Install
You need to download the 32 bit version of the file which is named AccessDatabaseEngine.exe, do not use the 64 bit download which is named AccessDatabaseEngine_X64.exe.
If you already have the Office 2010 64 bit drivers installed you will get an error that says “You cannot install the 32-bit version of Microsoft Access Database Engine 2010 because you currently have 64-bit Office products installed. ” when you attempt to run the installation that looks like this.
If you get this error, you can still install the Excel 2010 32 bit drivers, however you have to do it via a command line. To do this, use Windows Search to search for “cmd” which should display “Command Prompt” in the results, right click on the result and select “Run as Administrator”, type in administrator credentials if prompted.
Once you are in the command prompt, you need to navigate to the location where you have download the “AccessDatabaseEngine.exe” file. I normally will move it to an easier to find location than my downloads folder, in this case I have created a “temp” folder and saved the file into my c:temp folder. So, in order to get into that folder, I type in “cd c:temp” and hit enter, the command line should look like this after that.
Then you need to run the installation using the /quiet switch. To do that type in the following into the command window “AccessDatabaseEngine.exe /quiet”, the command prompt will look like this before you hit enter.
Press Enter and the 32 bit installer will run without displaying any errors. The installer will open and show that it’s running, however you will not get any sort of confirmation that the installation has completed. Once the command prompt window is available again, you can close it.
If you have any questions, please reach out to support@eonesolutions.com