Loading multiple xlsx files

[danielnevitt]

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

[sunny_talwar]

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)

[Anonymous]

Hi Daniel,

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

Load multiple excel files from Multiple excel sheetsinto QV

[danielnevitt]

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

[sunny_talwar]

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?

[danielnevitt]

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

[sunny_talwar]

Step 1:

Step 2:

[danielnevitt]

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

[sunny_talwar]

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);

[Anonymous]

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

Next

Regards

Neetha