Loading CSVs from a Folder

[mathew101]

Firstly thank you for taking the time to read this.

I'm coming from Power BI & Excel, to Qlik Sense and I'm currently having difficulty with the following: -

Loading all CSVs from a folder, pull data out from each files name and use this as a data column

From the file name

• Extract the text between two characters
• Transform this text to a date data type
• Place these dates as a column into the data for that file, repeating the date for in row in the file
• Repeat this for all files in the folder
• Be able to filter out key file/s from the this process, and from the data load operation

With Power BI or Excel, the ETL tool Power Query/Query Editor makes this so easy, via a GUI driven process.  With Qlik Sense I'm rather stumped.

I understand the process for loading from a file (using the wildcard * on the file name), but I can't see how to combine this with my above requirement.

Regards,

Mathew

[Gysbert_Wassenaar]

Basically something like if you do a wildcard load

MyData:
LOAD
   *,
   FileName() as Source,
   Date(Date#(TextBetween(FileName(), '<', '>'), 'YYYYMMDD'),'MM/DD/YYYY') as DateFromFileName
FROM
    [LIB://MyCSVFiles\*.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq)
    ;

Replace < , >,  YYYYMMDD and MM/DD/YYYY with the appropriate characters and date format strings you need.

If you  want to exclude specific files then you could do two things:

1. Load all the data and then use a preceding load to filter out the data from those specific files

MyData:
LOAD * WHERE NOT WildMatch(Source, '*MySpecificFile1.csv','*MySpecificFile2.csv') ;
LOAD
   *,
   FileName() as Source,
   Date(Date#(TextBetween(FileName(), '<', '>'), 'YYYYMMDD'),'MM/DD/YYYY') as DateFromFileName
FROM
    [LIB://MyCSVFiles\*.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq)
    ;

2. Use a For - Next loop and add an If-then within the loop with conditions to filter out the files you don't want to load.

See this for example code of such a For-Next loop: https://community.qlik.com/t5/QlikView-App-Development/loop-through-to-load-all-files-from-a-folder-...