3 Replies Latest reply: Jan 6, 2011 9:43 AM by s j RSS

    Multiple File reading

      I have a scenario , would like to know the best practice.

      I have a folder which is input folder for QV . There everyday new excel files are dropped from other application. QV should refresh its dashboards with new input excel files everyday. The input files to QV having names appended with date but data format is same.

      How the data loading should be so that loading is proper with minimal change in QV application?

      Please suggest.

        • Multiple File reading

          Try this

          This is for csv-file

          so change the .csv to .xls

          and (ansi, txt, delimiter is ';', embedded labels, msq)

          SUB doDir(dir)

          FOR EACH file in filelist('$(dir)\*.csv')
          LET vBasename = subfield( subfield('$(file)', '\', -1), '.', 1);
          filelist:
          LOAD '$(vBasename)' as basename,
          '$(file)' as file
          AUTOGENERATE 1
          ;
          NEXT
          END SUB

          CALL doDir('C:\Dir')

          // Each file represents a table.
          if fieldvaluecount('basename')>0 then
          FOR i = 1 to fieldvaluecount('basename')
          TableSE:
          LET vBasename = fieldvalue('basename', i);
          LET file = fieldvalue('file', i);

          LOAD
          Dealer,
          SALES_PERSON_NR,
          OPTION_TYPE
          from [$(file)] (ansi, txt, delimiter is ';', embedded labels, msq);

          NEXT

          drop table filelist;

            • Multiple File reading

              Thanks for the reply . Need few more suggestion. If in a folder everyday QV expects new files with in same format with daily records. Now after one year we might require to have Qtrly as well as yearly.

              Hence what should be correct startegy for file handling. My plan is each day read daily record and conctenate of data in qvd file after reading archive the file nto different folder. When we run qtrly or yearly trend load the data from .qvd files. Please suggest if there is any better approach.