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)





                                    '$(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



                                    (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.