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(Applicatio n.ExeName) +FormatDat eTime('mmy yyy".sql', Now);
Index := 0;
try
sl.LoadFromFile(CdrImportE dit.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(L crAccess)) ) = LcrAccess then
dest := copy(dest,1+StrLen(PChar(L crAccess)) ,StrLen(PC har(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(CallDa y));
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(ChargeTota l, 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(ChargeTota l, ffFixed, 10, 2)+''', '''+band+''', '''+SiteNum+''')');
// Show SQL inserts
LB_Query.Lines.Add(CallIns ert);
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(CallIns ert);
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)the n 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;
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------
Forgive me the long code example.
Code----------------------
{*************************
Start of collecting call data information from
file
**************************
procedure TMainForm.CdrButtonOKClick
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(Applicatio
Index := 0;
try
sl.LoadFromFile(CdrImportE
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)
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(L
dest := copy(dest,1+StrLen(PChar(L
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(CallDa
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(ChargeTota
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(ChargeTota
// Show SQL inserts
LB_Query.Lines.Add(CallIns
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(CallIns
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)the
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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;
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;
ASKER
Thanks Calinutz, I am all sorted out.
ASKER