CSV and Excel import loop

This is an example of reading through a list of csv and excel files and appending them to an inmemory table.

The project can be used as inspiration and starting point if you need to read through a list of csv files (or excel files) and consolidate these in a table.

Script:

DEBUG
declare @countasint
declare @filename asstring

/* Get handle to list files in folder */
DATASOURCE filelistconn=DOTNET CONNECTION 'TARGIT.FileList.FileListConnection'''

/*  List all files in the csvs folder */
IMPORT csvfiles=filelistconn.{select*from[d:\transactions\csvs]where directory='current'}

/* Declare connection to CSV file so we can load CSVs */
DATASOURCE csvLocalFile = DOTNET CONNECTION 'TARGIT.CSV.CsvConnection'''

while csvfiles.eof=false    /* Loop over all CSV files */  
        SET @filename=csvfiles.fullpath
        print'Loading the file '+@filename
        /* Import the CSV file into the table csvFolder */        
        IMPORT csvFolder = csvLocalFile.{select*from[@@filename]}   /* Import each file into the table csvFolder  */
        movenext csvfiles  
LOOP

IMPORT excelfiles=filelistconn.{select*from[d:\transactions\excel]where directory='current'}
while excelfiles.eof=false    /* Loop over all CSV files */  
        SET @filename=excelfiles.fullpath
        print'Loading the file '+@filename
        DATASOURCE excelfile = DOTNET CONNECTION 'TARGIT.ADONET.Excel.ExcelConnection''datasource='+@filename+';'
        IMPORT excelFolder = excelfile.{select*from[Sheet1]}  /* Import each file into the table excelFolder  */
        CLOSE excelfile
        movenext excelfiles
LOOP

Download project: CSV and Excel import loop.improj

Note: The project cannot be executed out-of-the-box, since it comes without connection managers - but is meant for inspiration only.