EONE BLOG

Tech Tuesday: Source File Name from Folder Data Source


A very common question that the Support Team will get is:

“I’m using a SmartConnect map using a folder data source with csv files.  Is there any way to get the filename that each document came from?”

The answer is, surprisingly, no.

Unfortunately, there isn’t a built-in variable that SmartConnect uses that has this information that we could just reference.  To get the actual file name for a record, we will have to code it ourselves.

I can think of a couple different ways to do this:

  1. Don’t use a folder data source map. Instead make a map that mimics the behavior of a folder data source map by using scripts.

  2. Modify the actual source file with scripting to add a new column with the source file name added

Don’t use a folder data source

This seems to go against the whole idea of using a folder data in the first place.

But this is a clever idea and better yet, it is already mostly complete.

The premise in that article was that the user would like to use a ‘custom odbc query’ which we can run on a file data source but not a text file folder data source map.  The solution was to copy the file from a folder to the name of the file that the ODBC Query on the map was expecting.

But in our case, this works the same in that now that we have read the file name using the map pre- script, we could copy that original file name into a custom global variable such as GBL_FILENAME.

Modify the actual source file


To me, this seems like the easiest solution.  If we just had the file name in the source, we can reference it like any other column.  The “trick” then would be – how do we get it there?

We can again use a “Before Map” task similar to the code in the first section.  However, in this example we won’t rename the file.  Instead, we will open the file and read the contents looking for a column named “FileName”.  If there is a column named that, we can skip the file.  Otherwise we add it and then add the filename data to the rows.

Map Pre-Script

Dim sourceFile As String = “D:\eone\test\folder_ds_test\source\”

Dim list_line As Object
Dim i As Int16

For Each fi As FileInfo In New DirectoryInfo(sourceFile).GetFiles(“*.csv*”)
Dim linesList As New Object
Dim newList As New System.Collections.Generic.List(Of String)

i = 0

linesList = System.IO.File.ReadAllLines(fi.FullName)

‘if the column list already includes FileName then exit
If linesList(0).Contains(“FileName”) Then
Continue For
End If

For Each list_line In linesList
If i = 0 Then
newList.Add(list_line + “,FileName”)
Else
newList.Add(list_line + “,” + fi.Name)
End If
i = i + 1
Next

‘overwrite existing file with my new modified version
System.IO.File.WriteAllLines(fi.FullName, newList.ToArray())

Next

Return True

The above script would be the Map Pre-script.  Adjust the sourceFile path to be the path that your map is looking for the source files.  Also on the “GetFiles()” method, this is the file filter for the GetFiles method that filters on only “.csv” files.  If there were .txt and other files (which I would never recommend with a folder data source), the query wouldn’t find those files to change them.
Figure 1:Files modified by the script

After running the map with the above script, we can see in the source folder there were three files initially.  The newfile.txt wasn’t modified because of the “.csv” filter but the newfile2.csv was modified to include the new FileName column that was populated with the actual file name.

There is an existing feature request to add this ability so you should vote and comment on that, but until then hopefully one of these methods will suffice.

Best Regards,
Patrick Roth
eOne Support



Want to know more? Email me at pat.roth@eonesolutions.com. 

Leave a Comment




RECENT POSTS


Tech Tuesday: Accessing the Real-time Request Log in SmartConnect.com
Tech Thursday: New Field Types in Extender
Integrations from Excel to Microsoft CRM - A Match Made in Heaven
eOne GPUG Summit Sessions
SmartConnect 2018 now compatible with Microsoft Business Central October Release

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

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 Extender Enterprise Flexicoder GP GPUG integration integration manager Meet the Team Microsoft Microsoft dynamics crm Microsoft Dynamics GP MSDYNGP 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.