Skip to content


Tech Tuesday: Using a Script to Replicate a Folder Data Source

A request that we have seen occasionally from different clients is that they want the flexibility of a folder data source, but the functionality of an ODBC connection when using a flat file as a data source. We’ve written a script that can be applied to any flat file (Excel, text, XML, etc.) which will allow a map to read data from a file no matter what it’s named, while allowing a standard ODBC connection to be used in the map setup process.

If you aren’t familiar with a folder data source or an ODBC data source, here is a quick explanation of each data source type and its benefits. A folder data source allows a client to specify an entire folder as a source, rather than an individual file. This allows multiple files to be read in at once to SmartConnect, and those files will be read in no matter what the file names are. The drawback is that all the data will be pulled from each file, you cannot setup a filter or query to return only specific rows from each file. Meanwhile, an ODBC connection allows filtering of the data when connecting to the specific file. WHERE clauses and other restrictions can be used in the source. One of the drawbacks of this method is that the source file needs to have a specific name, otherwise the map will not recognize it.

The script that is included in this article is meant to be setup at the pre-map task level. The script will take the first file in a specified folder and rename and/or move it to another folder. The idea behind it is that you can use this script to rename different files to one standard name, and then have the map execute and process using an ODBC connection to that specific file. It combines many of the benefits from each data source type into one process. The only drawback is that it will read in one file at a time, but the map can be scheduled more frequently if there are multiple files that need to be processed by the map in a short period of time.

An example where this could be used is in the screenshot below. We are receiving an export file of customers that need to be imported into our system each day. The files are timestamped as part of the filename. Due to the data in the files, we don’t want to use a folder data source as we need to filter the information down further.

We will setup the map to read data from a file named “GP_Customers”. We will then setup the following script as a pre-map task to ensure that we take the first file and rename it to GP_Customers and move it to the correct folder before processing.


‘Declare the original folder the files are located in 
Dim fileFolder As String = “C:\SmartConnect Data\Demo\Dynamics GP\Data\” 

‘Declare the folder that the map is pointed at, along with the desired file name 
Dim newFileFolder As String = “C:\SmartConnect Data\Demo\Dynamics GP\Map Source\” 
Dim newFileName As String = “GP_Customers.txt” 

‘Get the directory info from the info provided 
Dim fileName As String 
Dim dirInfo As New IO.DirectoryInfo(fileFolder) 
Dim files = dirInfo.EnumerateFiles() 

‘Grab the first file from the directory 
For Each f as FileInfo in files 
fileName = f.Name 
Exit For 


‘Delete the file from the destination location if a file is already there 
If File.Exists(newFileFolder & newFileName) Then 
File.Delete(newFileFolder & newFileName 
End If 

‘Move the file 
File.Move(fileFolder & fileName, newFileFolder & newFileName) 

return true 


The sections that would need to be modified are the first couple lines where the fileFolder, newFileFolder, and newFileName variables are declared. fileFolder should be set to the folder where the files are originally being creating. newFileFolder should be set to the directory that SmartConnect is looking at for the source (it could be the same as the original folder). Finally, newFileName should be the name of the file that SmartConnect is expecting in the ODBC query.

This script should be set on the pre-map task section of the map and will automatically move one file per map run to the desired data source folder. You will want to setup a task to move the file out of the folder on completion of the map as this script is setup to delete the file from the destination folder it is copying the file to if it already finds one there with the same name.

The script itself is commented throughout and can be edited as needed to fit whatever scenario might require some additional configuration.


  1. Dan Tomlin on April 24, 2017 at 2:26 pm

    1. you can’t delete the file on the map completion because if there is no file at all when the map runs, it throws an error…

    2. maybe when using a folder data source, you can be allowed to utilize ODBC drivers…

  2. Lorren Zemke on May 2, 2017 at 1:31 pm


    You are correct, if you use the actual Folder Data Source, the file will already be moved. In this instance, you would use a data source such as Text File or Excel, or an ODBC data source that points to the Text File or Excel File. We are replicating the functionality that currently exists with the SmartConnect Folder Data Source but with a map pre script and map post success/failure scripts to handle instances where the Folder Data Source does not work as you note in your second item.


  3. Tanya Henderson on January 9, 2019 at 8:24 am

    Thanks Chris Hanson this worked perfectly except I have to remap the fixed length file format. I did also stack the functionality to add the name of the file to file as a column before using this feature. These blogs are so very helpful!

  4. Boris on May 14, 2021 at 9:48 am

    Hi, is it possible to get the map to loop to go through all files in the folder in the script? I’m thinking of creating a post map task to call itself, but not sure that would work, or if a modification has to be put into the script to stop it if no file is found.

  5. Patrick Roth on May 24, 2021 at 11:00 am


    Well the map wouldn’t loop thru no.
    However you could use a map post task for “run map” and have the map run itself again (it’ll let you).

    You’d have to make sure that you don’t get yourself into an infinite loop of course – that might be tricky.

Leave a Comment


Meet the eOne Team at BC Tech Days 2023
Tech Tuesday: Building My First Matrix Report
Overcoming the Top Challenges of Zendesk Integration
Accessing Historical Dynamics GP Data in NetSuite: Using Popdock
Popdock's Top 10 New Features


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



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 Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships Partner All Hands Call Popdock promotions release SalesForce SmartConnect SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training Zendesk

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.