Solved

Problem reading lines in text file

Posted on 2004-09-21
4
231 Views
Last Modified: 2012-05-05
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
Comment
Question by:lloydie-t
  • 2
4 Comments
 
LVL 11

Accepted Solution

by:
calinutz earned 125 total points
ID: 12118335
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
 

Author Comment

by:lloydie-t
ID: 12118498
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
 
LVL 12

Expert Comment

by:esoftbg
ID: 12121313
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
 

Author Comment

by:lloydie-t
ID: 12126289
Thanks Calinutz, I am all sorted out.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now