topic Re: recursive file loading in QlikView

recursive file loading

Wed, 11 Jul 2012 08:20:06 GMT

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.

Re: recursive file loading

nstefaniuk — Wed, 11 Jul 2012 08:53:39 GMT

Your only issue is : "how to browse sub-folders recursively?"

this one could help http://community.qlik.com/message/192877#192877

Re: recursive file loading

hic — Wed, 11 Jul 2012 09:14:13 GMT

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

Re: recursive file loading

Wed, 11 Jul 2012 09:14:54 GMT

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...

Re: recursive file loading

nstefaniuk — Wed, 11 Jul 2012 09:17:49 GMT

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.

Re: recursive file loading

swuehl — Wed, 11 Jul 2012 09:22:01 GMT

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.

Re: recursive file loading

Wed, 11 Jul 2012 09:23:22 GMT

@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.

Re: recursive file loading

hic — Wed, 11 Jul 2012 09:27:49 GMT

Sorry, there is a comma too much on that row. It should be:

for each vExtension in 'xls', 'xlsx'

Re: recursive file loading

Wed, 11 Jul 2012 09:43:36 GMT

Thanks. The problem really seems to be the sheet names in the various Excel files.

Re: recursive file loading

nstefaniuk — Wed, 11 Jul 2012 11:49:25 GMT

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

Re: recursive file loading

nstefaniuk — Wed, 11 Jul 2012 11:52:34 GMT

These ones too :

http://community.qlik.com/message/223935#223935

http://qlikviewnotes.blogspot.fr/2008/09/loading-multiple-excel-sheets.html