Dynamic Excel import loop with TARGIT InMemory

In this example, I loop through a collection of Excel files in a directory and load the data from each Excel file into one TARGIT InMemory table. 

I use two data drivers for this example. FileList to create a table with the Excel files and then the Excel data driver to load each Excel file. 

The Excel data driver is linked to a single Excel file. Therefore I have to create and close the Excel driver for each file in the loop.

The directory with the source Excel files is defines in the variable "filepath" in line 7, so you can change the path and run the script.

LOAD ASSEMBLY 'TARGIT.FileList.dll' //server
LOAD ASSEMBLY 'TARGIT.Excel.dll' //server

DECLARE @filepath AS STRING
DECLARE @filename AS STRING

SET @filepath = 'C:\Transactions\Excel'

DATASOURCE [Filelist] = DOTNET CONNECTION 'TARGIT.FileList.FileListConnection' 'loadasstring=false;'
IMPORT [Excelfiles] = [Filelist].{SELECT * FROM [@@filepath] WHERE filename='*.xlsx' AND directory='current'}

WHILE Excelfiles.EOF=FALSE /* Loop over all Excel files */ 
  SET @filename = Excelfiles.fullpath
  PRINT 'Loading the file ' + @filename
  DATASOURCE [Excelfile] = DOTNET CONNECTION 'TARGIT.Excel.ExcelConnection' 'ignoreemptyvalues=false;datasource=' + @filename + ';detectionrowscount=100;skiplines=0;hasheaders=True;loadasstring=true'
  IMPORT [STAGE_Excel_Person] = Excelfile.{SELECT * FROM []} 
  CLOSE Excelfile
  MoveNext Excelfiles
LOOP

// Remove list of Excelfiles from final database
Drop Excelfiles

SAVE

Please note:

Each Excel file is expected to have the same data structure. You could consider to add try catch to the loop to avoid the script from stopping, if a file has a different data structure.

Data is loaded as strings, so we expect you to cast data with SAFECAST afterwards, but that is not the focus in this article.

I've added "SAVE" in the end of the script, so it can run on it's own. But you might want to leave SAVE out, if you copy this script into an existing ETL flow.

Demo data:

Below this article you will find "excel_example_data.zip" containing two small Excel files (file1.xlsx and file2.xlsx) that you can use for testing.