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

[prieper]

"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?)

[peterderrington]

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?

[Kushal_Chawda]

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

[peterderrington]

Here are some blank versions of the base file.

[peterderrington]

Just added them above

[prieper]

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

[peterderrington]

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.

[tyagishaila]

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

TAB1:

LOAD *

FROM

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

NEXT

[tamilarasu]

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

[peterderrington]

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