topic Re: How to import multiple sheets from multiple excel files? in QlikView

How to import multiple sheets from multiple excel files?

peterderrington — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to import multiple sheets from multiple excel files?

Kushal_Chawda — Wed, 24 May 2017 09:09:25 GMT

see this

Dynamically Loading Multiple Excel Files

Re: How to import multiple sheets from multiple excel files?

prieper — Wed, 24 May 2017 09:12:57 GMT

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

Re: How to import multiple sheets from multiple excel files?

ahaahaaha — Wed, 24 May 2017 09:14:24 GMT

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

Re: How to import multiple sheets from multiple excel files?

peterderrington — Wed, 24 May 2017 09:30:05 GMT

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

Re: How to import multiple sheets from multiple excel files?

peterderrington — Wed, 24 May 2017 10:26:43 GMT

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

Thanks.

Re: How to import multiple sheets from multiple excel files?

Kushal_Chawda — Wed, 24 May 2017 10:33:26 GMT

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

Re: How to import multiple sheets from multiple excel files?

peterderrington — Wed, 24 May 2017 10:52:52 GMT

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?

Re: How to import multiple sheets from multiple excel files?

Kushal_Chawda — Wed, 24 May 2017 10:57:19 GMT

I think you can use below to load both extensions

FOR EACH vFileExtension IN 'xlsx','xlsm'

Re: How to import multiple sheets from multiple excel files?

peterderrington — Wed, 24 May 2017 11:01:40 GMT

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.

Re: How to import multiple sheets from multiple excel files?

prieper — Wed, 24 May 2017 11:18:19 GMT

"April Causes_xlnm#_FilterDatabase" is not a sheetname, but an internal table used by Excel.

So you need to bring in a check to load only the "valid" names (is it the "#" in the name?)

Re: How to import multiple sheets from multiple excel files?

peterderrington — Wed, 24 May 2017 11:23:37 GMT

Each file has the following sheets with data in:

Jan

Feb

Mar

etc etc

and then

Jan Causes

Feb Causes

Mar Causes

etc etc

the * Causes sheets take data from the relevantly named initial sheet.

How would i build in a check to load only the valid names?

Re: How to import multiple sheets from multiple excel files?

Kushal_Chawda — Wed, 24 May 2017 11:25:29 GMT

if you can share two sample file xlsx and xlsm. I will try to do some checks

Re: How to import multiple sheets from multiple excel files?

peterderrington — Wed, 24 May 2017 11:25:44 GMT

Here are some blank versions of the base file.

Re: How to import multiple sheets from multiple excel files?

peterderrington — Wed, 24 May 2017 12:27:52 GMT

Just added them above

Re: How to import multiple sheets from multiple excel files?

prieper — Mon, 29 May 2017 08:02:51 GMT

Hi Peter,

just pimping my previous aircode

Data: LOAD * INLINE [FileName]; // Dummytable allowing later concatenation

FOR EACH sWorkBook IN FILELIST('MyDirectory\MyFilePattern.XLS*) // Wildcards allowed. last "*" covers xlsx and xlsm.

FOR EACH sSheetName IN 'Jan', 'Feb', 'Mar', ....'Nov17', 'Dec' // why is there a name like "Nov17"?

Data_normalized:

LOAD CROSSTABLE (Day, RedGreen, 7)

FROM

$(sWorkBook)

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

Data:

CONCATENATE (Data) LOAD

'$(sWorkBook)' AS WorkBook,

'$(sSheetName)' AS WorkSheet

RESIDENT

Data_normalized;

DROP TABLE Data_normalized;

NEXT sSheetName

NEXT sWorkSheet

Not quite clear, why the sheetnames changes from Aug, Sept, Oct then to Nov17, i.e. adding the year.

CROSSTABLE-functionality does not work together with CONCATENATE, therefore databases need to be splitted.

In the second part you may also add a general datefield, think that you may have to calculate numeric expressions first (Months probably via a mapping-table as "Sept" or "Nov17" is probably not recognized by QV, Days by KEEPCHAR(Day, '1234567890') .

Peter

Re: How to import multiple sheets from multiple excel files?

peterderrington — Tue, 30 May 2017 09:10:53 GMT

Thanks Peter,

i'll take a look at that now. The Nov17 was an error i had overlooked, I've altered the naming to match the rest of the convention (now just reads Nov).

We'll give it a try and see what happens.

Re: How to import multiple sheets from multiple excel files?

tyagishaila — Tue, 30 May 2017 09:22:01 GMT

FOR EACH vTabs IN 'JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP'

TAB1:

LOAD *

FROM

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

NEXT

Re: How to import multiple sheets from multiple excel files?

tamilarasu — Tue, 30 May 2017 09:24:50 GMT

Hi Peter,

Try this,

SUB ScanFolder(vPath)

FOR EACH vFileExtension IN 'xlsx'

FOR EACH vFile IN FILELIST(vPath & '\*.' & vFileExtension);

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

tables:
SQLtables;
DISCONNECT;

Data:
Load '' as Temp AutoGenerate 0;

LET vSheetCount = NoOfRows('tables');

FOR i = 0 to $(vSheetCount) -1

LET vSheetName = replace((peek('TABLE_NAME', $(i), 'tables')),chr(39)&chr(39),chr(39));
If(WildMatch(vSheetName,'*$')) then

LET vSheetName = replace(replace(purgeChar(peek('TABLE_NAME', $(i), 'tables'), chr(36)),'#','.'),chr(39)&chr(39),chr(39));

If (Wildmatch(vSheetName,'*Jan*','*Feb*','*Mar*','*Apr*','*May*','*Jun*','*Jul*','*Aug*','*Sep*','*Oct*','*Nov*','*Dec*')) then
Concatenate(Data)
LOAD *,
'$(vSheetName)' as Sheet, '$(vFile)' as FileName
FROM [$(vFile)] (ooxml, embedded labels, table is [$(vSheetName)]);
End if
ENDIF

NEXT i

DROP TABLE tables;
Drop Field Temp;

NEXT vFile
NEXT vFileExtension

END SUB

CALL ScanFolder('C:\Users\Tamil\Desktop\Test') ;

Re: How to import multiple sheets from multiple excel files?

peterderrington — Tue, 05 Sep 2017 10:53:48 GMT

Sorry for the delay in getting back to you.

I've tried that script and unfortunately its giving me an error message.

Any ideas?

Connecting to Excel Files;DBQ=\\wanweb1@90\FlowTracker\Shared Documents\01 Tracker Sheets\Master Tracker.xlsx
Connected
Temp << Tables-ODBC;dsn=excel files;dbq=\\wanweb1@90\flowtracker\shared documents\01 tracker sheets\master tracker.xlsx; 79 Lines fetched
Data << aef3-6e93-a8cf-0bc3 0 Lines fetched
Data << LookUp 14 Lines fetched
Data << April Causes 401 Lines fetched
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])
Data << August Causes 788 Lines fetched
Error: File Extdata.cpp, Line 2259