Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
starting in one given directory that has n sub directories i want to load one specific column (that exists in each tab; lets call it "TARGET") of all tabs of all xls files in each directory. I want to ignore all other file formats. The xml files have different names.
The values of that one column i want to store in a table RESULT that has two columns: TARGET (with the field's value) and SOURCE (containing the relative path together with the filename to the source xls file)
Hope the challange is clear
Thanks for some input.
Your only issue is : "how to browse sub-folders recursively?"
this one could help http://community.qlik.com/message/192877#192877
sub BrowseDirectory (Root)
for each vExtension in 'xls', 'xlsx',
for each vFileName in filelist (Root&' \*.' &vExtension)
Load
'$(vFileName)' as Name,
FileSize( '$(vFileName)' ) as Size,
FileTime( '$(vFileName)' ) as FileTime
autogenerate 1;
Load
'$(vFileName)' as Name,
*
FROM [$(vFileName)] (biff, embedded labels, table is TARGET);
next vFileName
next vExtension
for each vDirectory in dirlist (Root&' \*' )
call BrowseDirectory (vDirectory)
next vDirectory
end sub
call BrowseDirectory ('C:')
This aand - additionally - the tabs in the various Excel files have different names. So i assume i also need a way to go through all tabs in an Excel file. Some have only one tab, others might have 3 or 4 tabs...
Do you know the tab names ? I think it's not possible to access a tab without knowing it's name, when it's possible to access a column without knowing it's name.
I think you need to use an approach as described here:
http://community.qlik.com/message/223618#223618
i.e. do a ODBC or OLEDB Connect and browse your tables.
@henric: Thanks for the script part, but this gives me an error on for each vExtension in 'xls', 'xlsx', - any idea, why ?
@nstefaniuk: Tabs can unfortunately have almost any name. No convention there. I was hoping there is a way to loop though an excel file. The column i am looking at however has the same name in all files and tabs.
Sorry, there is a comma too much on that row. It should be:
for each vExtension in 'xls', 'xlsx'
Thanks. The problem really seems to be the sheet names in the various Excel files.
This one could help http://community.qlik.com/message/112289#112289
Have you XLS or XLSX files ? For XLS the @<id> method works well.
If you don't specify the sheet, it will read only the first one.
You can try with the ODBC method