[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

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;
------------------------------------------------------------------------------------------------------
0
lloydie-t
Asked:
lloydie-t
  • 2
1 Solution
 
calinutzCommented:
Place a OpenDialog,AdoQuery and a button on a form and insert this code:

Procedure WriteToDB(ix:integer;x1:string;x2:string);
begin
Form1.AdoQuery1.Active:=false;
Form1.AdoQuery1.SQL.Clear;
Form1.AdoQuery1.Sql.Add('insert into yourtable (idx,str1,str2) values('+IntToStr(Round(ix/2))+','+QuotedStr(x1)+','+QuotedStr(x2)+')');
Form1.AdoQuery1.ExecSQL;
end;

procedure TForm1.Button1Click(Sender: TObject);
var
  F: TextFile;
  S,S1: string;
  idx:integer;
begin
  if OpenDialog1.Execute then            { Display Open dialog box }
  begin
    AssignFile(F, OpenDialog1.FileName); { File selected in dialog }
    Reset(F);
    idx:=0;
   Repeat
    Readln(F, S);                        { Read first line of file }
    if not eof(f) then Readln(F, S1);    { Read second line of file }
   WriteToDB(idx,s,s1);
   idx:=idx+2;
   until eof(f);
    CloseFile(F);
  end;
end;

Didn't test it ... but it should work
It reads the file line by line and inserts the 2 line strings in a table of your choice together with the "index".
Success
0
 
lloydie-tAuthor Commented:
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
0
 
esoftbgCommented:
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;
0
 
lloydie-tAuthor Commented:
Thanks Calinutz, I am all sorted out.
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now