It's pretty easy to load all of the files from a folder into QlikView. For example, if you have a number of Excel spreadsheets in the f:\Finance\monthly_summaries folder and each spreadsheet contains a tab named EOM_SHEET and the column names on that tab are consistent in each spreadsheet file then these lines in your loadscript will load all of the spreadsheets into your QlikView report:
Directory f:\Finance\monthly_summaries;
For each ExcelFile in filelist ('*.xls')
EOM_HISTORY:
Load * From $(ExcelFile) (biff, embedded labels, table is [EOM_SHEET$]);
Next ExcelFile;
Note that this looping through the files of the folder will only use filenames that end with a .xls extension. So, any other documentation or data files in the folder will be ignored. The spreadsheets can contain other worksheet tabs too -- only the worksheet tab named EOM_SHEET will be loaded into QlikView. Hundreds of spreadsheet files can be loaded fairly quickly this way. As always with QlikView object names spelling counts and it's case-sensitive, e.g. EOM_SHEET is not the same thing as EOM_Sheet. If you're worried that not all of the spreadsheets use the same column headings then it might be best to code each column heading as a specific field name in the Load statement. That will make QlikView call it an error if the column headings in any of the spreadsheets don't match the expected field names. The error is usually better than loading inconsistent data. The Directory statement and Load statement can also make use of variables to make them more flexible to handle folder and file naming conventions. The load statement, of course, can be made to fit most situations with Where clauses and Group by options.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
In a comment, Guido correctly pointed out that this syntax achieves the same result and it's simpler:
EOM_HISTORY:
Load * From f:\Finance\monthly_summaries\*.xls (biff, embedded labels, table is [EOM_SHEET$]);
Check out the other comments to this posting.
QlikviewNotes has moved!
-
My QlikviewNotes blog has relocated to the Qlikview Cookbook site. All
links to the qlikviewnotes.blogger.com site have been automatically
redirected to th...
10 years ago
7 comments:
Hi Tim,
Look at this:
EOM_HISTORY:
Load * From f:\Finance\monthly_summaries\*.xls (biff, embedded labels, table is [EOM_SHEET$]);
This will work too...
Any opinion on how to load ALL worksheets from one workbook without using vbscript?
Regards
Guido
Thanks, Guido;
I've never come across the need to do that. You could do something like this:
EOM_HISTORY:
Load * From f:\Finance\monthly_summaries\*.xls (biff, embedded labels, table is [sheet1$]);
Load * From f:\Finance\monthly_summaries\*.xls (biff, embedded labels, table is [sheet2$]);
Load * From f:\Finance\monthly_summaries\*.xls (biff, embedded labels, table is [sheet3$]);
Normally, people put different kinds of data into the various worksheets of a spreadsheet. That would make it unusual to want to load them all into the same table of a QlikView Report. Although, I can imagine a situation where you might want to load specific columns from all of the worksheets; for example loading the customer name and customer number from each worksheet of a spreadsheet.
Hi Tim,
You can get filename without the "For Each" syntax by using the filename() function -- which returns the name of the file currently being read.
Load *, filename() as name
From f:\Finance\monthly_summaries\*.xls (biff, embedded labels, table is [EOM_SHEET$]);
There are a number of other File*() functions that return things like basename or full path.
Keep up the qlogging!
-Rob
Guido,
Re your question on reading multiple sheets. Here's a technique I got from the
QlikCommunity Forum.
ODBC CONNECT TO [Excel;DBQ=C:\temp\qv\workbook.xls];
tables:
sqltables;
FOR i = 1 to fieldValueCount('TABLE_NAME')
LET sheetName = purgeChar(fieldValue('TABLE_NAME', i), chr(39));
Sales:
LOAD * FROM C:\temp\qv\workbook.xls (biff, embedded labels, table is [$(sheetName)]);
NEXT
-Rob
Make sure to check Loading multiple Excel Sheets
Hi
I have a quick question. After loading a number of excel files this way how do i compare the information in QV?
For instance I have total sales from two different year loaded from two different excel files. I would like to present both of them in a graph, but in my table i only see one "total sales" box.
Hope you can help a fairly new member of the QV family.
Regards
Andreas
Can i load specific excel file? For example, i've 3 file excel in my folder (1.xls, 2.xls and 3.xls) and i want to load just 1.xls e 2.xls.
Can i do that?
Thank you very much!
Post a Comment