Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Problem reading lines in text file

Posted on 2004-09-21
4
237 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

765 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