3 Replies Latest reply: May 12, 2015 5:42 AM by Friedrich Hofmann RSS

    Loading Excel files with similar names

    Adrian Buzer

      Hi

       

      I have several excel files all with the filename Supplier*.xlsx.  I want to load all the files without using separate script for each one.  This is my current script to load the first two files which is repeated for every other file.

       

      Suppliers:

      CrossTable(Supplier, Supply, 4)
      LOAD *
      FROM
      [C:\Supplier A.xlsx]
      (
      ooxml, embedded labels, header is 1 lines, table is [Sheet1]);

      SupplierTemp:
      CrossTable(Supplier, Supply, 4)
      LOAD *
      FROM
      [C:\Supplier B.xlsx]
      (
      ooxml, embedded labels, header is 1 lines, table is [Sheet1]);

       

      concatenate (Suppliers)
      load *
      resident SupplierTemp;
      drop table SupplierTemp;

       

      Please help

       

      Thanks

        • Re: Loading Excel files with similar names
          Friedrich Hofmann

          Hi,

           

          as far as I can see, your LOADs are all identical but for the filename?

          => In that case try using a FOR EACH loop with a filelist.

          - Build a searchmask like >> LET v_searchmask = 'supplier*.xlsx'; <<

          - Then use the DIRECTORY '[path_to_your_directory]' command to make the directory where these files are located your pwd (that is not strictly
            necessary, you could do it otherwise, but it makes things a bit easier) - just remember to reset that at the end (just
             type > DIRECTORY; < without anything else)

           

          Then you go like

          >>>>>>>>>>>>>>>>>>>>

          For Each v_file in filelist ('$(v_searchmask)')

            [your LOAD]

          FROM

          $(v_file)

          [specifier];

          NEXT

           

          That will parse your directory for any files matching that searchmask and load everyone in the same manner.

          HTH

          • Re: Loading Excel files with similar names
            Jose Federico Moreno Penzo

            Try this:

             

            Suppliers:

            ...

            FROM
            [C:\Supplier*.xlsx]
            (
            ooxml, embedded labels, header is 1 lines, table is [Sheet1]);

              • Re: Loading Excel files with similar names
                Friedrich Hofmann

                Hi Jose,

                 

                that will also work, of course.

                That just loads all files that match this filelist - the same effect, basically.

                The reason I'm not doing this with the * anymore is that in case anything goes wrong because of an error in one of the files, then, if you have the *, the log will not tell you which list the error is in and you'll have to look into them all.

                We once had something like this with 11 lists and it was a lot of work looking at them all and finding out in which one there was a typo ...

                Otherwise, that will work fine and it is certainly easier to write than that FOR EACH loop.

                Best regards,

                 

                DataNibbler