topic Re: Loading multiple xlsx files in QlikView

Loading multiple xlsx files

danielnevitt — Wed, 02 Sep 2015 10:05:45 GMT

Hi,

We have a file (edw2iceyyyy-mm-dd-hh-mm-ss) that is loaded to location on daily basis (see attachment).

Is it possible to load multiple files using variables?

For example, I would like to load the most recent 10 files and I wondered if there was a simple way to do this?

Any help would be much appreciated.

Regards,

Daniel

Re: Loading multiple xlsx files

sunny_talwar — Wed, 02 Sep 2015 10:10:00 GMT

Try it with the wild card (*)

FROM Path\edw2ice*.xslx;

Just make sure that all the Excel files have same format (tab names, field names etc)

Re: Loading multiple xlsx files

Anonymous — Wed, 02 Sep 2015 10:15:25 GMT

Hi Daniel,

I have had the same issue. The below thread help:

Load multiple excel files from Multiple excel sheetsinto QV

Re: Loading multiple xlsx files

danielnevitt — Wed, 02 Sep 2015 10:37:41 GMT

Thank you for the responses. I think adding the * solves my issue.

One other question I have, is there a way to either split the below data when loaded or in the qvw file?

I'm trying to show for example; RPBQV, 0000000000012015083141, OXD, 201508 as separate columns.

Thanks,

Daniel

Re: Loading multiple xlsx files

sunny_talwar — Wed, 02 Sep 2015 10:40:47 GMT

I think if you share a sample, we might be able to give you a better solution. Can you share one of the excel files?

Re: Loading multiple xlsx files

danielnevitt — Wed, 02 Sep 2015 10:49:52 GMT

I don't seem to have the option to add an excel file?

Re: Loading multiple xlsx files

sunny_talwar — Wed, 02 Sep 2015 11:07:36 GMT

Step 1:

Step 2:

Re: Loading multiple xlsx files

danielnevitt — Wed, 02 Sep 2015 11:11:30 GMT

Thanks Sunny. Unfortunately I don't see that option on my screen:

Re: Loading multiple xlsx files

sunny_talwar — Wed, 02 Sep 2015 11:23:41 GMT

Try this script:

Table:

LOAD SubField(A, ' ', 1) as 1,

SubField(A, ' ', 2) as 2,

SubField(A, ' ', 3) as 3,

SubField(A, ' ', 4) as 4

FROM

Community_178850.xlsx

(ooxml, no labels, table is Sheet1);

Re: Loading multiple xlsx files

Anonymous — Wed, 02 Sep 2015 11:31:51 GMT

hi daniel,

FOR EACH file in FileList('filepath\edw2ice*.xlsx');

//In order to get the file information from SQLtables command making use of the ODBC connection format

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

tables:

SQLtables;

DISCONNECT;

FOR i = 0 to NoOfRows('tables')-1

LET sheetName = Peek('TABLE_NAME', i, 'tables');

Table:

Load * ,

FileBaseName()as FIle,

FileDir() as Dir,

FileName() as File_Name,

'$(sheetName)' as Sheet_name,

SubField(Field,' ', 1) as Field1,

SubField(Field,' ', 2) as Field2,

SubField(Field,' ', 3) as Field3,

SubField(Field,' ', 4) as Field4

From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

NEXT i

Regards

Neetha