EONE  BLOG

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 

Next 

‘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.

2 Comments

  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

    Dan,

    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.

    Thanks,
    Lorren

Leave a Comment




RECENT POSTS


Modify Excel Templates for compatibility with Excel Add-in
Meet eOne at Summit in Nashville
New Releases of SmartList Builder and Extender
VIP Roundup Party at Summit 2017 in Nashville
Tech Tuesday: Troubleshooting SmartList Builder Lists that Return No Data

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
SmartList Builder 2013 New Feature of the Day: Auto Updating SmartList

CATEGORIES

ARCHIVES

open all | close all

TAGS

#fargo 2017 CRM Demo Dynamics Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News eOne Spotlight ERP Events Excel Excel Report Builder Extender GP GPUG integration integration manager Meet the Team Microsoft Microsoft dynamics crm Microsoft Dynamics GP ODBC Office Relationships partners release SalesForce SalesForce.com scripting SmartConnect 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.