topic Re: Loading xml files from several folders in QlikView

Loading xml files from several folders

Thu, 31 Jan 2013 09:47:03 GMT

Hi,

I have a folderstructure something like this c:\Orders\<Year>\<Monthnumber>\<Daynumber>\ and in every Daynumber folder there are several xml files which all have the same fields.
New folders are automatically created when needed (a new Daynumber folder every day, a new Monthnumber folder everey month and a new Year folder every year)

When loading xml files, two tables are generated.

Example:

MessageLine:
LOAD Key_Field,
    fieldname1,
    fieldname2,
    ....
    fieldname n
FROM (XmlSimple, Table is [Messages/Message/MessageLine]);

Messages:
LOAD Key_Field,
     fieldnameA,
     fieldnameB,
     ......

FROM (XmlSimple, Table is [Messages]);

This gives me all the xml files located in C:\Orders\2012\12\01\

But how can I load all the xml files from every Daynumber folders (for all years, months and days)?

Re: Loading xml files from several folders

Gysbert_Wassenaar — Thu, 31 Jan 2013 10:10:42 GMT

See here for a script to load files from a directory and its subdirectories.

Re: Loading xml files from several folders

Thu, 31 Jan 2013 12:16:28 GMT

Thanks,

made some small modifications, this seems to work for me:

sub ScanFolder(Root)

for each FileExtension in 'xml'

for each FoundFile in filelist( Root & '\*.' & FileExtension)

MessageLine:

Load

                                        '$(FoundFile)'               as [FilenameWithPath],
                                        '$(FileExtension)'           as [Extension],
                                           other fields....

                                              .....
                        FROM [$(FoundFile)] (XmlSimple, Table is [Messages/Message/MessageLine]);


                           Messages:

                             Load
                                   fields....

FROM [$(FoundFile)] (XmlSimple, Table is [Messages]);

next FoundFile

next FileExtension

for each SubDirectory in dirlist( Root & '\*' )

call ScanFolder(SubDirectory)

next SubDirectory

end sub

Call ScanFolder('C:\Orders') ;