Dyanamic load of excel files from folder

[#1]

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

[#2]

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

[#3]

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

[rwunderlich]

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

Load

Name,

Id

from

(ooxml, embedded labels, table is Display);

-Rob

[quiquehm]

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

[prieper]

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

[quiquehm]

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

[prieper]

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

[quiquehm]

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

[tripatirao]

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