3 Replies Latest reply: Jul 22, 2014 10:17 AM by Bill Markham RSS

    Loading a comma seperated value file

    Simon Brulotte

      Hi,

      I am mesmerised (seems that it doesn't take me alot) by this:

       

      Here is my load statement created from the wizard:

       

      LOAD [Num compte ],

          
      [Date fact ],

          
      [Num tél mo],

          
      [Nom                 ],

          
      [Prénom       ],

          
      [Num référ           ],

          
      [Abonné de groupe              ],

          
      [Catégorie      ],

          
      [Sous-catégorie ],

          
      [Total-redr ],

          
      [Tot fr mens],

          
      [Fr option  ],

          
      Durtemps,

          
      [Fr temps   ],

          
      [Fr données ],

          
      [Fr itinér  ],

          
      [Fr int itin],

          
      [Fr d'inter ],

          
      [Autres fr  ],

          
      [Esc total  ],

          
      [Tot fr cour],

          
      MessTxt,

          
      [Tot MessTxt],

          
      [Dur don ],

          
      Durinter,

          
      [Dur itin],

          
      Intdndur,

          
      Fraisintdon,

          
      [Vol TP  ],

          
      [Utilis TP  ],

          
      [TPS        ],

          
      [TVH        ],

          
      [TVH ÎPÉ Tel],

          
      [TVHO Tel   ],

          
      [TVHBC Tel  ],

          
      [TVO        ],

          
      [TVQ - téléc],

          
      [TVQ - autre],

          
      [TVP ÎPÉ    ],

          
      [TVP CB     ],

          
      [TVP - Sask ],

          
      [TVP - Man  ],

          
      [Tx autr pay],

          
      [Du 10-4P],

          
      [Frais 10-4P],

          
      [Du 10-4G],

          
      [Frais 10-4G],

          
      [It 10-4 ],

          
      [Fr it 10-4 ],

          
      [Min jour],

          
      [Min W/E ],

          
      [Min soir],

          
      MinTrAnt,

          
      MinAdmTr,

          
      MinAdmUt,

          
      MinExpFct,

          
      TotMinTr,

          
      [Min à part ],

          
      [Min part ut],

          
      [Mo à part  ],

          
      [Mo part ut ],

          
      [Évén à part],

          
      [Év part ut ],

          
      [App à part ],

          
      [App part ut]

      FROM

      [MY FILE PATH\0101352803_07012014_MOB_TEST]

      (
      txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);

       

      My file is attached to the post.

       

       

      Nothing else in the QVW.

       

      I get an error message saying the first field not found...

       

      Can someone try it?

        • Re: Loading a comma seperated value file
          Ashfaq Mohammed

          Hi,

           

          Try below

           

          Directory;
          LOAD [Acct Nbr], 
               [Invc Date], 
               [Mobile Nbr], 
               Surname, 
               [Given Name], 
               [Ref Nbr], 
               [Group Subscriber], 
               Category, 
               SubCategory, 
               [Adj App Ttl], 
               [Mth Chg Ttl], 
               [Ftr Chg Ttl], 
               [Air Dur], 
               [Airtime Chg], 
               [Data Chg], 
               [Roamer Chg], 
               [Roamer LD], 
               [LD Chg], 
               [Other Chgs], 
               [Disc Ttl], 
               [Ttl Charges], 
               [Txt Msg], 
               [Txt Msg Amt], 
               [Data Dur], 
               [LD Dur], 
               [Roam Dur], 
               Rmdatdur, 
               [Rm datachrg], 
               PktDtVol, 
               [Pkt Dat Amt], 
               GST, 
               HST, 
               [HST-PEI Tel], 
               [HST-ON Tel], 
               [HST-BC Tel], 
               ORST, 
               [QST - Telec], 
               [QST - Other], 
               [P.E.I. PST], 
               [BC PST], 
               Sask, 
               Manitoba, 
               [Foreign tax], 
               [10-4P Dr], 
               [10-4 P Amt], 
               [10-4G Dr], 
               [10-4 G Amt], 
               [10-4 Rmr], 
               [10-4 Rm Amt], 
               [Min day], 
               [Min W/E], 
               [Min nght], 
               RLprevmin, 
               RLminelig, 
               RLminused, 
               RLBexpmin, 
               RLtotal, 
               [Grp Sh Min], 
               [Sh Min Used], 
               [Grp Sh MB], 
               [Sh MB Used], 
               [Grp Sh Evts], 
               [Sh Evt Used], 
               [Grp Sh Call], 
               [Sh Call Use]
          FROM
          [0101352803_07012014_MOB_TEST.txt]
          (txt, codepage is 1252, embedded labels, delimiter is ',', msq, filters(
          Remove(Row, RowCnd(CellValue, 1, StrCnd(null)))
          ));
          

           

          Regards

          ASHFAQ

          • Re: Loading a comma seperated value file
            Jonathan Dienst

            Hi

             

            You need to take the trailing spaces out of the fieldnames.

             

            [Num compte ] -> [Num compte],

             

            HTH

            Jonathan

              • Re: Loading a comma seperated value file
                Bill Markham

                Ort maybe do it like this ?

                 

                This ignores the first row and assumes the second row contains the labels.

                 

                It all depends on what you wish to acheive.

                 

                 

                Directory;

                LOAD [Num compte ,Date fact ,Num tél mo,Nom                 ,Prénom       ,Num référ           ,Abonné de groupe              ,Catégorie      ,Sous-catégorie ,Total-redr ,Tot fr mens,Fr option  ,Durtemps,Fr temps   ,Fr données ,Fr itinér  ,Fr int itin,Fr d'inter ,Autres fr  ,Esc total  ,Tot fr cour,MessTxt,Tot MessTxt,Dur don ,Durinter,Dur itin,Intdndur,Fraisintdon,Vol TP  ,Utilis TP  ,TPS        ,TVH        ,TVH ÎPÉ Tel,TVHO Tel   ,TVHBC Tel  ,TVO        ,TVQ - téléc,TVQ - autre,TVP ÎPÉ    ,TVP CB     ,TVP - Sask ,TVP - Man  ,Tx autr pay,Du 10-4P,Frais 10-4P,Du 10-4G,Frais 10-4G,It 10-4 ,Fr it 10-4 ,Min jour,Min W/E ,Min soir,MinTrAnt,MinAdmTr,MinAdmUt,MinExpFct,TotMinTr,Min à part ,Min part ut,Mo à part  ,Mo part ut ,Évén à part,Év part ut ,App à part ,App part ut]

                FROM

                [0101352803_07012014_MOB_TEST.txt]

                (txt, codepage is 1252, embedded labels, delimiter is ';', msq, header is 1 lines);