How to import multiple sheets from multiple excel files?

[peterderrington]

Hi,

Now i'm sure this has been asked before but having had a quick look i cant see an obvious answer....

• I have several spreadsheets
• Each spreadsheet covers a different Organisational Unit
• Each spreadsheet has sheets named the same (although not necessarily in the same order)
• They are all monthly data that is filled in daily

I need to be able to import the data and then manipulate it in the same way i can with Excel - e.g. view all totals, separate by month or organisational unit etc etc

I know that QlikView can do this but i just don't know the best way to do it. I've seen something about qvd's but again i don't fully understand how to use these.

Can anyone help?

Many thanks,

Peter

[Kushal_Chawda]

see this

Dynamically Loading Multiple Excel Files

[prieper]

You may load multiple workbooks by the use of wildcards "*", like

LOAD * FROM MyWorkbook*.xlsx;

Usually it is more transparent to have it in a loop created by FILELIST

sheetnames must be knows and then encorporated into a loop.

aircode

FOR EACH sWorkBook IN FILELIST('MyDirectory\MyFilePattern.XLSX)

FOR EACH sSheetName IN 'A', 'B', 'C'

     Data:

          LOAD

               *,

               '$(sWorkBook)'     AS WorkBook,

              '$(sSheetName)'     AS WorkSheet

     FROM

          $(sWorkBook)

          (ooxml, embedded labels, table is $(sSheetName));    

NEXT sSheetName

NEXT sWorkSheet

Peter

[ahaahaaha]

Hi Peter,

It is difficult to advise on such a broadly defined task without seeing the data and the desired result (this is how to cure the patient on the phone), but if you have only these conditions

• I have several spreadsheets
• Each spreadsheet covers a different Organisational Unit
• Each spreadsheet has sheets named the same (although not necessarily in the same order)
• They are all monthly data that is filled in daily

,

I would have uploaded all the tables to one table with the addition of the [Division] field, which would fill in according to the loaded table.

Regards,

Andrey

[peterderrington]

Thanks for this.

I'm going through the steps but obviously doing something wrong as i get this error message:

Table not found error

Table 'Data' not found

STORE Data into \\pdcqrs01\d$\QlikView Documents\Source Documents\Peter\Tracker\FullData.qvd

and then:

Table not found

DROP TABLES statement

The following message is then displayed in the 'Executing Script' box:

Field 'A' not found

Drop Field A

Table 'Data' not found

STORE Data into \\pdcqrs01\d$\QlikView Documents\Source Documents\Peter\Tracker\FullData.qvd

Table not found

DROP TABLES statement

What am i doing wrong

[peterderrington]

OK, and so how would i easily go about that?

Thanks.

[Kushal_Chawda]

check this things

Make sure file path for excel and QVD is proper

LET vExcelFilePath = 'D:\Test';
LET vQVDFilePath='D:\Test';

Check file extension "XLS" or "XLSX"

FOR EACH vFileExtension IN 'xlsx'

Check ODBC connection is working fine

ODBC CONNECT TO [Excel Files;DBQ=$(vFile)];

[peterderrington]

Ok;

The file path was where the issue seems to be - totally my ridiculousness - i'd put the whole path of the file not the folder! Sorry.

However i have now brought up another issue. Some of the files are .xlsm and others are .xlsx - I think i've got round that by just adding 'xlsm' after the 'xlsx' bit is that right?

Is there anyway i can get it to ignore 2 specifically named excel files that are unfortunately in the main folder?

[Kushal_Chawda]

I think you can use below to load both extensions

FOR EACH vFileExtension IN 'xlsx','xlsm'

[peterderrington]

Yeah, i've done that but the error message i'm getting now is:

Error: File Extdata.cpp, Line 2259

CONCATENATE(Data)

LOAD  *,

FILEBASENAME() AS FileName,

'April Causes_xlnm#_FilterDatabase' AS Sheet_name

FROM \\wanweb1@90\FlowTracker\Shared Documents\01 Tracker Sheets\Master Tracker.xlsx(ooxml, embedded labels, table is [April Causes_xlnm#_FilterDatabase])

I seem to get this error message for every sheet and every file as it goes through them.