For Each Load of similar files

[#1]

Hello,

I was wondering if anyone can help me.

Im currently loading a bunch of excel files with rather few info each one, that has the same format and the same order, but the table name is different on the last chars.

For example, every excel file has from 1 to 100 lines of data with a few columns that represent daily extractions.

There fore, the table name ends with the day of the extraction.

For example:

extraction_20150827 from today, but last days name was extraction_20150826.

I want to be able to extract the info in a for each file in the table that start with extraction_.

Does anyone know how can i achieve that?

Or would i have to manually edit each table name.

Thanks for your help,

[maxgro]

for Each file in filelist ('extraction_*.xlsx')

  LOAD *

  FROM

  $(file)

  (ooxml, embedded labels, table is Sheet1);

NEXT;

[chaper]

For each File in filelist ('Folder_Path')

Folder:

Load '$(File)' as Name,

FileTime( '$(File)' ) as FileTime

autogenerate 1;

next File

LatestFile:

first 1

Load

Name,

Name as NewFile,

Date(FileTime,'MM/DD/YYYY') as NewFileTime,

Resident Folder

Order By FileTime DESC;

drop table Folder;

I use this script to increment  latest file only to already exisitng data.Hope it helps you

[#4]

the problema are the table names, not the file names

[Alejandro_Hernández]

Hi Mariana,

Read this other post (by Massimo too)

https://community.qlik.com/message/589469#589469

[harsh44_bhatia]

i think you have a case where there are multiple worksheet with one excel file with names varying according to date.

well for the multiple files u can use the solution as given by maxgro and for multiple worksheet you would need to have another sub code that will generate the worksheet names which will be the value for table name during load statement.

something as follows:

let vlastdate=date('20150826','YYYYMMDD')

.

.

temp:

load

date($(vlastdate),'YYYMMDD') +iterno()-1 as filenamesuffix

autogenerate 1

while date($(vlastdate),'YYYMMDD') +iterno()-1 <=date(today(),'YYYMMDD');

now u may use create sub loop to build your worksheet name

let vtabname= 'extraction_' & fieldvalue('filenamesuffix',loopcunter);

now you this as dollar sign expansion in table name

  LOAD *

  FROM

  $(file)

  (ooxml, embedded labels, table is [$(vtabname)]);