1 Reply Latest reply: Oct 2, 2015 9:01 AM by Marcus Sommer RSS

    How generate txt files with the function peek for the filename ?

    Aziz Medjerab

      Hello,

       

      I want to generate text files delimited by tabulations based upon a table in a QV application.

       

      This Table contains over 6000 rows, but into these rows there are only 6 departments, I would like generate one file per department.

       

      The name of the file will be the department name ('Department'_Actuals.txt) and I don't want export the labels into the files.

       

      To do that, I load my table like this :

       

      ForfaitsTmp:

      CrossTable (Mois,Montant)

      LOAD

      Type & '|' & CodeClient & '|' & Media & '|' & Dépt & '|' & Fichier as key,

      Jan,

      Fev,

      Mar,

      Avr,

      Mai,

      Juin,

      Juil,

      Aou,

      Sep

      FROM '$(vQVDPath)\$(vProjet)\forfait.xls'

      (biff, embedded labels, table is Sheet1$);

       

      Forfaits:

      Load SubField(key,'|',1) as Type,

          SubField(key,'|',2) as CodeClient,

          SubField(key,'|',3) as Media,

          SubField(key,'|',4) as Dépt,

          SubField(key,'|',5) as Fichier,

          Mois, num(Montant,'# ##0.00000')

      RESIDENT ForfaitsTmp;

       

      STORE Forfaits INTO '$(vQVDPath)\$(vProjet)\'Department name'_Actuals.txt' (delimiter is '\t');

       

      I'm not quite good in scripting, can somebody help me pls ?

       

      Thanks,

        • Re: How generate txt files with the function peek for the filename ?
          Marcus Sommer

          You could by crosstable-load the number of columns specify which will be loaded normally and you didn't need to create this key and split it then again. Try therefore the following:

           

          ForfaitsTmp:

          CrossTable (Mois,Montant, 5)

          LOAD * FROM [$(vQVDPath)\$(vProjet)\forfait.xls] (biff, embedded labels, table is Sheet1$);

           

          for i = 1 to fieldvaluecount('Dept')

               let vTable = fieldvalue('Dept', $(i))

               $(vTable):

               Load Type, CodeClient, Media, Dept, Fichier, Mois, num(Montant,'# ##0.00000') as Montant

               Resident ForfaitsTmp;

               store $(vTable) into [$(vQVDPath)\$(vProjet)\ $(vTable)_Actuals.txt] (txt, no labels, delimiter is '\t');

          next

           

          The store-statement to text is limited and I'm not sure if it's considered the additionally entries with labels and delimiter but in general it should work.

           

          - Marcus