6 Replies Latest reply: May 5, 2016 3:54 AM by s Walter RSS

    Declaration of code lines

    s Walter

      Hi,

       

      i have some questions for the red lines in this code:

       

      Set vConcatenate = ;

      sub ScanFolder(Root)

                for each FileExtension in 'csv'

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

                                    FileList:

                                    $(vConcatenate)

                                    LOAD *, '$(FoundFile)' as SourceFile

                                    FROM [$(FoundFile)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

                                    Set vConcatenate = Concatenate;

                          next FoundFile

                next FileExtension

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

                          call ScanFolder(SubDirectory)

                next SubDirectory

      end sub

       

      Call ScanFolder('C:\Users\hic\Documents\2012\Work\QV Apps\DoDir') ;

       

      loop through to load all files from a folder and its subfolders?#

       

      Why use this code LOAD *, '$(FoundFile)' as SourceFile ?

      Normally it looks like this LOAD * from data1.csv;

       

      In this line FROM [$(FoundFile)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

      What does txt, codepage, msq, header mean?

       

      regards,

      René

        • Re: Declaration of code lines
          Marcus Sommer

          LOAD *, '$(FoundFile)' as SourceFile adds simply the source to the table. You could also use a filefunction like filebasename() for it - in each load which goes on a file.

           

          (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines) is the definition of the fileformat - this meant you have on this point various options to adjust it for your needs.

           

          - Marcus

            • Re: Declaration of code lines
              s Walter

              ok, thanks.

               

              What must i use if i have xlsx and csv files?

               

              regards,

              René

                • Re: Declaration of code lines
                  Jonathan Dienst

                  Import the file using the wizard. It will create the file format specifier for you. In your case, just choose one file to import, then copy and paste the specifier to the load statement in the loop. Then you can delete the load statement you created for the sample file.

                    • Re: Declaration of code lines
                      s Walter

                      I did it like you describe. I get always this error:

                      Error: File extdata.cpp, Line 2279: FileList: Concatenate LOAD *,...


                      But only if i load a xlsx file. If i load a csv file it works.


                      For csv i use this code:

                      FROM [$(Datei)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

                       

                      For xlsx this code:

                      FROM [$(Datei)] (ooxml, embedded labels, table is test));


                      regards,

                      René

                        • Re: Declaration of code lines
                          s Walter

                          This script runs:

                           

                          Set vConcatenate = ;

                           

                           

                          sub ScanFolder(Root)

                           

                           

                          for each FileExtension in 'csv','xlsx'

                           

                           

                            if FileExtension = 'csv' then

                             

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

                            

                            FileList:

                            $(vConcatenate)

                            

                            LOAD *, '$(Datei)' as SourceFile

                            FROM [$(Datei)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

                            

                            Set vConcatenate = Concatenate;

                           

                            next Datei

                            

                            elseif FileExtension = 'xlsx' then

                             

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

                             

                            FileList:

                            $(vConcatenate)

                           

                            LOAD *, '$(Datei)' as SourceFile

                            FROM [$(Datei)] (ooxml, embedded labels);

                             

                            Set vConcatenate = Concatenate;

                           

                            next Datei

                             

                            end if

                           

                           

                          next FileExtension

                           

                           

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

                            call ScanFolder(SubDirectory)

                            next SubDirectory

                           

                           

                          end sub

                           

                           

                           

                           

                          Call ScanFolder('C:\work');

                      • Re: Declaration of code lines
                        Marcus Sommer

                        The easiest way to get the right format-statement is to load one file of them normally - you will get a preview to check if the fields are correct interpreted - and then copy this format-statement into your loop-routine.

                         

                        If you didn't used different extension-statements within the filelist-loop filelist( Root & '\*.' & FileExtension) then you will need an appropriate if-loop on fileextension() to adress the right fileformat.

                         

                        - Marcus