2 Replies Latest reply: Jan 21, 2017 5:12 AM by S Satti RSS

    Automatic load files with additional static column

    Iñigo Elviro Bodoy

      Hi everybody:

       

      I need the help with one script.

       

      I need load several excell files, more than 100, with the same structure.

       

      For this I have used the following script which works very weel:

       

      Set vConcatenate = ;

      sub ScanFolder(Root)

                for each FileExtension in 'xls'

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

                                    FileList:

                                    $(vConcatenate)

                                    LOAD

                                    *,

                                    '$(FoundFile)' as SourceFile

                                    FROM [$(FoundFile)] (biff, embedded labels, header is 3 lines, table is Hoja1$) where [NUM. ROLLO]<>'0';

                             

                                    Set vConcatenate = Concatenate;

                          next FoundFile

                next FileExtension

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

                          call ScanFolder(SubDirectory)

                next SubDirectory

      end sub

      Call ScanFolder('C:\Users\inigoelv\Documents\Informes') ;

       

      The problem is in all files  are additional information in some specific cell value, it is only one value.

       

      When I try to insert that specific value as value common in every row of each file that doesn´t works:

       

      left join

                                    FIRST 1 LOAD

                                    F3 as Descripcion,

                                    F21 as Orden

                                    FROM [$(FoundFile)] (biff, embedded labels, header is 1 lines, table is Hoja1$);

      left join

                                    FIRST 1 LOAD

                                    @19 as MP

                                    FROM

                                    [$(FoundFile)]

                                    (biff, no labels, header is 1 lines, table is Hoja1$); 

      Depending of the position I inserted, some times freeze, some times works with the first file and no with the others, etc...

       

      Any solution?

       

      Thanks in advance.