Link to home
Start Free TrialLog in
Avatar of lloydie-t
lloydie-t

asked on

Problem reading lines in text file

I am having a problem looping through lines in a text file and using the info in an SQL database. The information is kept on two lines at a time, so therefore I am using the 'Index' Var to count each line , returning the index var to '0' each time the second line is read. The problem I am have is that although it is counting the lines correctly only the first 2 lines are being seen in the info. Any Ideas?
Forgive me the long code example.

Code-----------------------------------------------------------------
{*****************************************************
Start of collecting call data information from
file
******************************************************}


procedure TMainForm.CdrButtonOKClick(Sender: TObject);
var
  i: integer;
  J : integer;
  k : integer;
  CallInsert : Pchar;
  sl : TStringList;
  s : TStrings;
  Index :  Integer;
begin
  With CollexDB do
  Begin
    CollexDB := TliteDB.Create(self, 'collex.sdb');
  end;
  CdrButtonOK.Enabled := False;
  CdrImportEdit.Enabled := False;
  LB_query.Lines.Clear;
  Label4.Visible := False;
  sl := TStringList.Create;
  s := TStringlist.Create;
  SqlFileDate := ExtractFilePath(Application.ExeName)+FormatDateTime('mmyyyy".sql', Now);
  Index := 0;
  try
    sl.LoadFromFile(CdrImportEdit.Text);
    Progressbar1.Position := 1;
    j := 0;
    k := 0;
    ProgressBar1.Max := sl.Count;
    ProgressBar1.Visible := True;
    //loop though each line of text
    for i := 0 to sl.count - 1 do
    begin
        if GrpNum(sl[i],Index) <> '' then
        group_no := GrpNum(sl[i],Index);
        if StartNum(sl[i],Index) <> '' then
        start_no := StartNum(sl[i],Index);
        if PhoneNum(sl[i],Index) <> '' then
        ExtVar := PhoneNum(sl[i],Index);
        if ExtNum(sl[i],Index) <> '' then
        Exten := ExtNum(sl[i],Index);
        if DateOfCall(sl[i],Index) <> '' then
        CallDay := DateOfCall(sl[i],Index);
        if CallDir(sl[i],Index) <> '' then
        direction := CallDir(sl[i],Index);
        if Transfer(sl[i],Index) <> '' then
        trans := Transfer(sl[i],Index);
        if ExtNum(sl[i],Index) <> '' then
        extn_no := ExtNum(sl[i],Index);
        if TrunkNum(sl[i],Index) <> '' then
        trunk_no := TrunkNum(sl[i],Index);
      ddi := 'None';
        if PhoneNum(sl[i],Index) <> '' then
        dest := PhoneNum(sl[i],Index);
        if SQLLenofCAll(sl[i],Index) <> '' then
        SQLDuration := SQLLenofCAll(sl[i],Index);
        if RingLen(sl[i],Index) <> '' then
        ring_time := RingLen(sl[i],Index);
//      call_time := SQLDateOfCall(sl[i])+' '+TimeOfCall(sl[i]);
        if TimeOfCall(sl[i],Index) <> '' then
        CallTime := TimeOfCall(sl[i],Index);
        if SQLDateOfCall(sl[i],Index) <> '' then
        CallDate := SQLDateOfCall(sl[i],Index);
        if LenOfCall(sl[i],Index) <> -1 then
        Duration := (LenOfCall(sl[i],Index));
      k := k + 1;
      ProgressBar1.Position := k;
      Inc (Index);
      if Index>1 then begin
      call_time := Calldate+' '+CallTime;
        if  direction = 'Out' then
        begin

                if copy(dest,1,StrLen(Pchar(LcrAccess))) = LcrAccess then
                dest := copy(dest,1+StrLen(PChar(LcrAccess)),StrLen(PChar(dest)));


        CollexDB.DoQuery('select areacode, band, destination from stdcodes where areacode = (SELECT MAX(areacode) FROM stdcodes WHERE '''+dest+''' LIKE areacode || ''%'')');
        //if there is a match, run calculations
                if  CollexDB.RowCount > 0 then
                Begin
                TimeOfCall1 := StrToTime(CallTime);
                DayBand := DayOfWeek(StrToDate(CallDay));
                band := CollexDB.Results[0][1];
                dest_name := CollexDB.Results[0][2];
                CallBand := CheckCallBand(Callday, Calltime ); {uses CheckCallBand function}
                ChargeTotal :=  CallChargeFunc(CallBand, Band, trunk_no);

          // insert call data into database
                CallInsert:=PChar('Insert into call_data (direction, extn_no, trunk_no, ddi, dest, dest_name, duration, call_time, cost, band, site_id) values ('''+direction+''', '''+extn_no+''', '''+trunk_no+''', '''+ddi+''', '''+dest+''', '''+dest_name+''', '''+SQLduration+''', '''+call_time+''', '''+FloatToStrF(ChargeTotal, ffFixed, 10, 2)+''', '''+band+''', '''+SiteNum+''')');
                s.Add ('Insert into call_data (direction, extn_no, trunk_no, ddi, dest, dest_name, duration, call_time, cost, band, site_id) values ('''+direction+''', '''+extn_no+''', '''+trunk_no+''', '''+ddi+''', '''+dest+''', '''+dest_name+''', '''+SQLduration+''', '''+call_time+''', '''+FloatToStrF(ChargeTotal, ffFixed, 10, 2)+''', '''+band+''', '''+SiteNum+''')');

          // Show SQL inserts
                LB_Query.Lines.Add(CallInsert);

                end   //CollexDB.RowCount
          end //direction = out
          else
          begin
          if direction = 'In' then
          begin
          CallInsert:=PChar('Insert into call_data (direction, group_no, start_no, extn_no, trunk_no, trans, ddi, dest, duration, ring_time, call_time, cost, band, site_id) values ('''+direction+''', '''+group_no+''', '''+start_no+''', '''+extn_no+''', '''+trunk_no+''', '''+trans+''', '''+ddi+''', '''+dest+''', '''+SQLduration+''', '''+ring_time+''', '''+call_time+''', ''0.00'', ''Incoming'', '''+SiteNum+''')');
          s.Add ('Insert into call_data (direction, group_no, start_no, extn_no, trunk_no, trans, ddi, dest, duration, ring_time, call_time, cost, band, site_id) values ('''+direction+''', '''+group_no+''', '''+start_no+''', '''+extn_no+''', '''+trunk_no+''', '''+trans+''', '''+ddi+''', '''+dest+''', '''+SQLduration+''', '''+ring_time+''', '''+call_time+''', ''0.00'', ''Incoming'', '''+SiteNum+''')');
          LB_Query.Lines.Add(CallInsert);
          end;  //direction = in

          Index:=0;
      end; // Index>1

     end; //for i
    end; //try
  finally

        CollexDB.StartTransaction;
        for i:=0 to s.count -1 do
        CollexDB.Query (s[i]);
        CollexDB.Commit;
    CollexDB.Free;

                        if FileExists(sqlFileDate)then begin
                        AssignFile(SqlFile, SqlFileDate);
                        Append(SqlFile);
                        WriteLn(SqlFile, 'BEGIN TRANSACTION;');
                        for i:=0 to s.count -1 do
                        WriteLn(SqlFile, (s[i])+';');
                        WriteLn(SqlFile, 'COMMIT;');
                        CloseFile(SqlFile);
                        end
                        else begin
                        AssignFile(SqlFile, SqlFileDate);
                        Rewrite(SqlFile);
                        WriteLn(SqlFile, 'BEGIN TRANSACTION;');
                        for i:=0 to s.count -1 do
                        WriteLn(SqlFile, (s[i])+';');
                        WriteLn(SqlFile, 'COMMIT;');
                        CloseFile(SqlFile);
                        end;
    CdrButtonOK.Enabled := True;
    CdrImportEdit.Enabled := True;
    ProgressBar1.Visible := False;
    Label4.Visible := True;
    Label4.Caption := 'Import Complete';
    sl.free;
    s.free
  end;
end;
------------------------------------------------------------------------------------------------------
ASKER CERTIFIED SOLUTION
Avatar of calinutz
calinutz
Flag of Romania image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lloydie-t
lloydie-t

ASKER

Thanks for the input. Unfortunately the app relies on an DLL which needs to know which of the two lines it is processing, hence the reason for 'Var(sl[i],Index)' being the line number. I suppose I could change the whole structure of the DLL so that it also reads the seperate S and S1 vars. Is that feasable? In any case I will try this solution tommorrow. Thanks
I think you need a loop like this one:

      I  := 0;
      I1 := 1;
      while (I1<SL.Count) do
      begin
        if GrpNum(sl[i],Index) <> '' then
          group_no := GrpNum(sl[i],Index);
        //........  
        Inc(I,2);    // Increment by 2
        Inc(I1,2);  // Increment by 2
      end;

instead of

>    for i := 0 to sl.count - 1 do
>    begin
>        if GrpNum(sl[i],Index) <> '' then
>        group_no := GrpNum(sl[i],Index);
          //........  
>      end;
Thanks Calinutz, I am all sorted out.