4 Replies Latest reply: Feb 27, 2012 7:13 AM by Dave Riley RSS

    loading contents of multiple files

      hello have multiple files in a subdirectory that I have to load.

       

      Their names can be ONLY 'ABC' OR 'ABC.myfile'. I need to create a loop to load the info from these files (their content are all the same).

       

      Can you assist please? Not sure how to write the script.

       

      Thanx

        • loading contents of multiple files

          i just noticed that one of the fields I'm loading, does not have a field name in Excel (embedded header).To add to the above discussion, how can I dinamically give a field name (taken into consideration that all the other fields DO have embedded field names). I could put them in manually, but there are many files that I need to load.Thank you

          • loading contents of multiple files

            Hi,

             

            You can try using a Do..While statement for loading multiple fles from the same directory....

             

            Let file_counter=0;

            for file_counter =1

                 do

            while file_counter<n //n is the number of files in your sub directory

             

            Load * from file$(file_counter).xlsx;

            let file_counter=file_counter+1;

            loop

             

             

            Hope that helps.

             

            Regards,

            -Khaled.

             

             

            //Let a=0;

            //Let i=0;

            //do while i<4

            //a=a+1

            //loop   

              • loading contents of multiple files

                Thank you Khaled, Problem is, I have multiple files in the subdirectory (and I dont always know how many). I would just like to load all the files that contain 'HIST' in the file extension....it might be 'HIST001' and 'HIST002'.

                  • loading contents of multiple files
                    Dave Riley

                    You can use wildcards to select from multiple files, similar to ...

                     

                    LOAD Filename(),

                    @1 as Data1,

                    @2 as Data2

                    FROM

                    [*.hist*]

                    (txt, codepage is 1252, no labels, delimiter is '|', msq, header is 1 lines);

                     

                    Regarding the missing field name, if you try to load it as embedded labels you will get an error due to the missing column identifier.  If you say all the files are same structure, then load it with "no labels" and skip the first line (the header, shown as "header is 1 lines" above) and alias the fields such as @1 as Data1, @2 as Data2.

                     

                    flipside