Add data from a folder

[Anonymous]

Hello everyone!! A few days ago I have joined this community to which, every day, I am surprised more and more.

Wanting to do a test with my data I have found that I can not overcome this question that I tell you below.

I have a network disk folder a series of files, XLS, CSV and TXT that always have the same name and in case of being updated, they are replaced by a file with the same name. These data are used as dimensions.

But I have a folder where month to month files "report_2017_01" for January, "report_2017_02" for February and so on each month.

These files always have the same column structure and are therefore added one after another.

So far I have not found a way for Qlik to add me the data without having to touch the Data Model by hand.

Qlik can not solve it?

For the doubts I tell you that the way I could solve it was by using the PowerQuery (Excel add-in) which, if I told you to take data from a folder and find files that have the same structure, join them all in a single table . Got this what I do is for Qlik to read that table. 😞 but I do not like anything.

A big greeting for the whole community.

Martin.

[rahulpawarb · Accepted solution]

Hello Martin,

You can refer below given sample script:

//This will load all the files which begins with report_ name
Data:
LOAD Id,
...
report_*.xlsx
(ooxml, embedded labels, table is [Sheet1]);

Hope this will be helpful.

Regards!

Rahul

[jonathandienst]

You can use a wildcard load or a loop to load and concatenate multiple files with a similar structure. So for your monthly data files, you could load like this:

Fact:

LOAD 0 as Dummy Autogenerate 0;

Concatenate(Fact)

LOAD *,

  FileBaseName() as Source

FROM ...\*.xls

DROP Field Dummy;

If the date is not in the file, then you could parse the file name (in FileBaseName()) for the date; something like:

Date(Date#(Mid(FileBaseName(), 8, 7), 'yyyy_MM')) as Date,