recursive file loading

[#1]

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.

[nstefaniuk]

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

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

[hic]

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

[#4]

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

[nstefaniuk]

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.

[swuehl]

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.

[#7]

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

[hic]

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

for each vExtension in 'xls', 'xlsx'

[#9]

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

[nstefaniuk]

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