2 Replies Latest reply: Jan 31, 2013 7:16 AM by Kjetil Kleppe RSS

    Loading xml files from several folders

    Kjetil Kleppe

      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 [C:\Orders\2012\12\01\*.xml] (XmlSimple, Table is [Messages/Message/MessageLine]);

       

      Messages:
      LOAD Key_Field,
           fieldnameA,
           fieldnameB,
           ......
          
      FROM [C:\Orders\2012\12\01\*.xml] (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

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

            • Re: Loading xml files from several folders
              Kjetil Kleppe

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