topic Re: Dyanamic load of excel files from folder in QlikView

Dyanamic load of excel files from folder

Tue, 29 Jan 2013 14:24:53 GMT

We are following a manual process to load excel files from a folder every month. I have to make it automated. I need to load new excel file from that folder and append to the last months data....So basically I need to have history of files for all previous months and if a new excel file is added in that folder I need to load that dynamically with the QVW reload. The excel file is added monthly once and with different filenames.....How can I make it automated, the excel file does not have any date field to filter....Thanks

Re: Dyanamic load of excel files from folder

alexandros17 — Tue, 29 Jan 2013 14:29:30 GMT

let path_Alles = 'C:\FileName*.xlsx';

for each File in filelist (path_Alles)

AllData:

LOAD * FROM $(File) (ooxml, embedded labels, table is Display);

next File

Re: Dyanamic load of excel files from folder

Tue, 29 Jan 2013 15:48:15 GMT

I am using your code and just changed Path_Alles but its not working....Can you please check it....

I am getting error as File .\extdata.cpp,Line 1832

AllData:

Load * from C:\Test\IT_SEP.xlsx (ooxml, embedded labels, table is Display);

let

path_Alles = 'C:\Test\*.xlsx';

for each File in filelist (path_Alles)

AllData:

LOAD * FROM $(File) (ooxml, embedded labels, table is Display);

next File

Re: Dyanamic load of excel files from folder

alexandros17 — Tue, 29 Jan 2013 16:13:59 GMT

The code LOAD * FROM $(File) (ooxml, embedded labels, table is Display); is an example

you have to use "Table Files" and load your file.

Re: Dyanamic load of excel files from folder

Tue, 29 Jan 2013 16:29:18 GMT

Sorry, I am not Clear.....Let us take an example, If I have Name and Id as fields in my excel files

Load

Name,

from

$(File) (ooxml, embedded labels, table is Display);

Is this what you want me to do?

Thnx

Re: Dyanamic load of excel files from folder

alexandros17 — Tue, 29 Jan 2013 16:41:16 GMT

Hi,

in the script side, you have a button marked as "TABLE FILES", press it, then select your file xls, you will be guided, you can choose labels embedded and so on, then click finish, the script to load the file will be included in your document.

now you have to reload all data ...

Let me know.

Re: Dyanamic load of excel files from folder

Tue, 29 Jan 2013 16:45:59 GMT

I have 4 xls files and they ll be coming every month.....if I select 1 xls how ll I get all the months data....As you suggested I loaded 1 xls file so i had only 1 month of data....Now if the folder is updated with the new xls file how should I reload it.

Thanks

Re: Dyanamic load of excel files from folder

alexandros17 — Tue, 29 Jan 2013 16:52:06 GMT

I suppose that all the 4 files have the same structure, so the script to load the file is correct and the cycle for next will load all files in the directory

Re: Dyanamic load of excel files from folder

Anonymous — Tue, 29 Jan 2013 18:00:39 GMT

Hi 447.....,

it is possible to provide a sample excel file in month wise ,i will give script for that one.

Regards

Re: Dyanamic load of excel files from folder

Tue, 29 Jan 2013 18:05:15 GMT

Please find the attached files

Re: Dyanamic load of excel files from folder

Tue, 29 Jan 2013 18:14:02 GMT

let

path_Alles = 'C:\Test\*.xlsx';

for each File in filelist (path_Alles)

AllData:

LOAD

Valuation

Closing

FROM

(ooxml, embedded labels, table is Display);

next File

I used this script but able to get only 1 month i.e Sep month....Can you pleas echeck this Thankyou

Re: Dyanamic load of excel files from folder

Tue, 29 Jan 2013 18:33:50 GMT

I gave * in the place of IT_SEP its working grt.....Thankyou Alexandros

Re: Dyanamic load of excel files from folder

rwunderlich — Tue, 29 Jan 2013 18:36:10 GMT

Use a wildcard * in your file specification. That will load all files each time.

Load

Name,

from

(ooxml, embedded labels, table is Display);

-Rob

Re: Dyanamic load of excel files from folder

quiquehm — Sat, 07 Mar 2015 19:43:28 GMT

I am using this approach to load multiples excel files from a folder. The problem is when the number of files grows it is quiet inefficient to load all the files again and again ( everytime a new file is available in the folder the whole set of files is loaded again ). Do you know a better way to modify the script so it only loads the new added file(s) and append these to the previous loaded files ? This way the data load will be much faster. Maybe loading files and storing into a QVD file ...then using the files loaded in the QVD to compare with whatever files we have in the excel files folder so only new files are loaded and then apended to this QVD ?. Could someone provide a sample script to test ?

Thanks a lot

Re: Dyanamic load of excel files from folder

prieper — Sat, 07 Mar 2015 20:27:58 GMT

Aircode:

let path_Alles = 'C:\FileName*.xlsx';

Files: LOAD * FROM Files.qvd; // to be executed only if Files.qvd exist already, i.e. after the second run

for each File in filelist (path_Alles)

     IF NOT EXISTS (FileName, $(File)) THEN
           AllData:
           LOAD * FROM $(File) (ooxml, embedded labels, table is Display);

Files: // Add the current file-name to the list of files

LOAD '$(File)' AS FileName AUTOGENERATE 1;

END IF

next File

STORE Files INTO Files.qvd (qvd);

HTH Peter

Re: Dyanamic load of excel files from folder

quiquehm — Sat, 07 Mar 2015 21:50:26 GMT

Thanks for the script Peter, I will give it a try asap. The only thing I am not sure it will work is regarding my file names. They have this naming convention : YYYYMMDD_data.xlsx, being the first 8 caracters the date stamp ( manually created ) for every file. When loading the files I extract that string YYYYMMDD from every file name and I create a date field ( date_stamp ). This is why I thought maybe after loading the first run and storing to a QVD , I could use this QVD in a preceding load and do a LOAD Distinct date_stamp to get the list of date_stamps available in the QVD. Then I should compare this list with the current date_stamp ( YYYYMMDD ) available in my excel files folder and load only the new ones ....but this is the piece of code I dont know how to write.

Any suggestions ?

Thanks for your help

Re: Dyanamic load of excel files from folder

prieper — Sun, 08 Mar 2015 20:07:14 GMT

Hi Enrique,

this is exactly the purpose of my script, i.e. to load all filenames previously loaded add the new files to this table and then to store the combined data into a database for the next use.

Do not think that is makes a difference, whether it is a simple filename or has already a date encorporated, unless you may have several files per day.

Peter

Re: Dyanamic load of excel files from folder

quiquehm — Mon, 09 Mar 2015 18:44:51 GMT

Thanks for the clarification Peter. I believe I was confused by looking at the name of the file you declare in your variable path_Alles . You call it FileName*.xlsx , and I thought the name of the file would then need to be called whatever your file name is having a fixed name followed by a counter or something , like :

Snapshot01.xlsx

Snapshot02.xlsx

Snapshot03.xlsx

....etc

so I was interpreting it incorrectly. Now I see FileName* means whatever your excel file name is.

Thanks again, I will give it a try asap

Re: Dyanamic load of excel files from folder

tripatirao — Wed, 02 Nov 2016 10:58:36 GMT

Hi,

This code is not working for me.

let path_Alles = 'E:\CCMP\Source\*.xlsx';

Files:

LOAD Player,

Run

FROM

[..\QVD\AllData.qvd]

(qvd); // to be executed only if Files.qvd exist already, i.e. after the second run

for each File in filelist (path_Alles)

IF NOT EXISTS (FileName, $(File)) THEN

AllData:

LOAD * FROM $(File) (ooxml, embedded labels, table is Display);

Files: // Add the current file-name to the list of files

LOAD '$(File)' AS FileName AUTOGENERATE 1;

END IF

next File

STORE Files INTO Files.qvd (qvd);

Please help me Where I am doing wrong.

Regards

Tripati