Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Problem reading lines in text file

Posted on 2004-09-21
4
Medium Priority
?
245 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
[X]
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
  • 2
4 Comments
 
LVL 11

Accepted Solution

by:
calinutz earned 500 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

660 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