EONE BLOG

Tech Thursday: Source File Name from Excel Folder Data Source


In my last blog post, we discussed the problem of a folder data source and not knowing the source file name was that each row of data in the map came from.

In that post, the source file type was a text file, so it wasn’t terribly difficult to use .NET to read the folder file names and then modify the file to add that file name as a new column.

But what if our Folder Data Source was using an Excel file?

Some of what we learned from that previous article still applies, the code to find the files in that source folder is the same.

And if we leverage a bit of code from another of my previous posts, we had adjusted the output file column header on an Excel file destination.

With those two bits of previous code, I thought this was going to be an easy project.  Well it ended up being a bit tricky with a couple of mistakes – writing/debugging this was more challenging than what I was shooting for.

And if we were going to use an Excel File Folder Data Source, I thought that we’d make it a bit more interesting to dynamically find the source folder from the SmartConnect tables as well as which sheet name it should use instead of hardcoding the paths as I did previously.

As we started off with the Customers.xlsx previously, we will again.  But this time, we want a new FileName column that our customization code will populate.  For that, we will manually edit the file and add the new column and save the new Template file.

Figure 1: FileName added manually to the Template

 

Our source files will not have this column (well unless you can modify the source generation routine to include this information – then you won’t need this script!) and will look the same but without the new column initially.

Figure 2:Sample source file without FileName column

 

If we think about the steps we need to complete, we would need:

  1. Run a SQL query to determine the source folder for this map (or we can cheat and just hard code it in the script)
  2. Run a SQL query to determine the Sheet Name that this folder data source map is using (or just hard code)
  3. Write a new with a loop to find all the xlsx files in the source folder
  4. Open each of those same Excel files somehow, see if the FileName column exists, and if not then add the column and populate each row.

 

To determine the source folder, we can query the FolderDataSource table.

Figure 3: Get the Source Folder path

In our script, we also tacked on the trailing back slash, we could have done it here or the ADD_COLUMNS_TO_SOURCE script but I thought it best to get the entire path with trailing slash here.  Also notice that the results of this script are put into our custom global variable GBL_SOURCE_FOLDER.

The script to get the Sheet Name is similar and also put into a customer global variable – GBL_SHEET_NAME.


Figure 4: Get the Sheet Name selected on the folder data source

The SQL for this one was a bit trickier because the sheet name in the table is stored with the trailing $ at the end – such as Sheet1$.  When using COM automation of Excel as we are going to do in this article, we only want the actual name as seen in Excel – Sheet1.  As before, we could have easily removed it with .NET code in the ADD_COLUMNS_TO_SOURCE but thought it would be more efficient to take care of it on the SQL query right away by removing the last character.  However that was a bit trickier than expected because the QueryText field is a ‘text’ field that that doesn’t support the Substring() function in SQL.  So it had to be converted to a varchar first and then we could use the Substring() function.

Now that we have the source folder and sheet, we have the hard part left to do.  The code to find the source files in that source folder, open them, and then modify them with the file name and finally re-save the data.


Figure 5: Where the magic happens

Some highlights of this code section include:

  1. Uses the global variables retrieved via the SQL Tasks
  2. Instantiates a COM interface to Excel. With .NET, using the .NET assemblies for Excel is preferred however this would entail using a new Script Namespace which affects ALL maps, not just this one.  Because of that, using COM vs .NET interop is safer
  3. Uses System.File.IO to run a DirectoryInfo query to find all xlsx files in the source folder
  4. For each file found, loop through each rows and writing the current file name in a new FileName column that we would create. When finished, re-save the document.
  5. When finished with these pre-map tasks, the normal processing of the map would take over which would now include the new FileName column correctly populated.

 

Interesting things that we learned in this map

  1. Using COM automation on Excel to read/write the data
  2. How to find the source folder and sheet name by querying the SmartConnect database

You can download the map, the source files, as well as text files with copies of the scripts HERE.

 

Best Regards,
Patrick Roth
eOne

 

Leave a Comment




RECENT POSTS


SmartConnect.com: an Introductory Video
Tech Tuesday: Connecting to SmartConnect and SmartConnect Online From Excel
SmartConnect.com is Generally Available
Create Rockstar Integrations - Attend Our SmartConnect Bootcamp in Phoenix
Don't Miss These Key Sessions at Directions North America

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

#fargo 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 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.