• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 569
  • Last Modified:

Reading delimited string and convert to sql statement

I need some help reading delimited strings from a text file and each line to file as a sql statement.

I'm a bit lost, please find rubbish code below.

code------------------------------------------------
procedure TForm1.Button2Click(Sender: TObject);
var
 i,k : integer;
 StrNum: TStringList;
 stamptime: string;
 SqlFile : TextFile;
 F: TextFile;
 S1: string;
 s : TStrings;

begin
        s := TStringlist.Create;
    StrNum.LoadFromFile(Edit1.Text);
        ProgressBar1.Visible := True;
        Progressbar1.Position := 1;
        ProgressBar1.Max := StrNum.Count;
        for i := 0 to StrNum.count - 1 do
        begin
        StrNum.Delimiter := '|';
        StrNum.DelimitedText := StrNum[i];
        stamptime := IntToStr(Str2UnixTime(StrNum.Strings[12]));
        s.add('Insert into call_data (direction, group_no, start_no, extn_no, trunk_no, trans, ddi, dest, dest_name, duration, ring_time, call_time, stamptime, cost, band, site_id) values ('''+StrNum.Strings[0]+''', '''+StrNum.Strings[1]+''', '''+StrNum.Strings[2]+''', '''+StrNum.Strings[3]+''', '''+StrNum.Strings[4]+''', '''+StrNum.Strings[5]+''', '''+StrNum.Strings[6]+''', '''+StrNum.Strings[7]+''', '''+StrNum.Strings[8]+''', '''+StrNum.Strings[9]+''', '''+StrNum.Strings[10]+''', '''+StrNum.Strings[11]+''', '''+StrNum.Strings[12]+''', '''+stamptime+''', '''+StrNum.Strings[13]+''', '''+StrNum.Strings[14]+''', '''+StrNum.Strings[15]+''')');
        k := k + 1;
        ProgressBar1.Position := k;
        end;
end;

any Ideas
0
lloydie-t
Asked:
lloydie-t
  • 3
  • 2
  • 2
  • +2
2 Solutions
 
Wim ten BrinkCommented:
You need two stringlists for this. One to hold the file itself and another one that holds a single delimited line.

At this moment you're messing things up because you override the contents of StrNum with the contents of the first line in your code, split over multiple lines...

But you're going in the right direction, although you might want to check if you have 15 items in the final delimited stringlist. Example:

procedure TForm1.Button2Click( Sender: TObject );
var
  i: integer;
  StrNum: TStringList;
  StrLine: TStringList;
  stamptime: string;
begin
  StrNum := TStringlist.Create;
  StrLine := TStringlist.Create;
  StrLine.Delimiter := '|';
  StrNum.LoadFromFile( Edit1.Text );
  ProgressBar1.Visible := True;
  Progressbar1.Position := 1;
  ProgressBar1.Max := StrNum.Count;
  for i := 0 to Pred( StrNum.count ) do begin
    StrLine.DelimitedText := StrNum[ i ];
    while ( StrLine.Count < 15 ) do
      StrLine.Add( '' );
    stamptime := IntToStr( Str2UnixTime( StrLine.Strings[ 12 ] ) );
    with StrLine do
      s.add( 'Insert into call_data (direction, group_no, start_no, extn_no, trunk_no, trans, ddi, dest, dest_name, duration, ring_time, call_time, stamptime, cost, band, site_id) values (''' + Strings[ 0 ] + ''', ''' + Strings[ 1 ] + ''', ''' + Strings[ 2 ] + ''', ''' + Strings[ 3 ] + ''', ''' + Strings[ 4 ] + ''', ''' + Strings[ 5 ] + ''', ''' + Strings[ 6 ] + ''', ''' + Strings[ 7 ] + ''', ''' + Strings[ 8 ] + ''', ''' + Strings[ 9 ] + ''', ''' + Strings[ 10 ] + ''', ''' + Strings[ 11 ] + ''', ''' + Strings[ 12 ] + ''', ''' + stamptime + ''', ''' + Strings[ 13 ] + ''', ''' + Strings[ 14 ] + ''', ''' + Strings[ 15 ] + ''')' );
    ProgressBar1.Position := ProgressBar1.Position + 1;
  end;
end;

Above code is untested, though...
0
 
lloydie-tAuthor Commented:
Thanks for that.
Nearly worked, but for some reason even though i have set the delimiter as '|'  i also considers a space as a delemiter. Is there any way I can fix this.
 
0
 
Wim ten BrinkCommented:
A space as delimiter? Ewww. That could be a minor problem. But if you sure none of your text uses a space as part of the text value itself, you could use the StringReplace function to replace them.

StrLine.DelimitedText := StringReplace(StrNum[ i ], ' ', '|', [rfReplaceAll]);
or
StrLine.DelimitedText := StringReplace(StrNum[ i ], '|', ' ', [rfReplaceAll]);

The first one requires the delimiter to be set to '|' and the second one uses a space for all delimiters.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
lloydie-tAuthor Commented:
Sorry a bit of a typo. I meant that all spaces where be considered by the app to be delimiters and I did not want this.
for example

6003|Andy Smith|2004-11-23 16:41:00 comes out as '6003' 'Andy' 'Smith' '2004-11-23' '16:41:00'

when it should be '6003' 'Andy Smith' '2004-11-23 16:41:00'
0
 
Eddie ShipmanAll-around developerCommented:
Use this function to split your string into a stringlist without having a space as a delimiter:

{
 Function PosEx is Copyright (c) 2001 Borland Software
 Corporation
 PosEx searches for SubStr in S and returns the index
 position of SubStr if found and 0 otherwise.  If Offset is
 not given then the result is the same as calling Pos.  If
 Offset is specified and > 1 then the search starts at
 position Offset within S.  If Offset is larger than
 Length(S)then PosEx returns 0.  By default, Offset equals
 1.  

 FWIW, PosEx appears in Delphi 7.

}
function PosEx(const SubStr, S: string; Offset: Cardinal = 1): Integer;
var
  Tmp: PChar;
begin
  Result := 0;
  if (Offset > Cardinal(Length(S))) then exit;
  if Offset = 1 then
    Result := Pos(SubStr, S)
  else
  begin
    Tmp := StrPos(PChar(@S[Offset]), PChar(SubStr));
    if Tmp = nil then exit;
    Result := Cardinal(Tmp - PChar(@S[Offset])) + Offset;
  end;
end;

procedure Split(S, Delimiter: string; Strings: TStrings);
var
  P, OldP: integer;
  Token: string;
begin
  // Prevent any errors due to bogus parameters
  if (Strings = nil) or (Length(S) = 0) or (Length(Delimiter) = 0) then
exit;
  P := Pos(Delimiter, S);
  OldP := 1;
  while P > 0 do
  begin
    Token := Copy(S, OldP, P-OldP);
    Strings.Add(Token);
    // Don't call delete, instead save off P and search from
    // P + 1 to the end of S
    OldP := P + 1;
    P := PosEx(Delimiter, S, OldP);
  end;
  if P = 0 then
    Strings.Add(Copy(S, OldP, Length(S)));
end;

{
In the above implementation of Split we avoid calling Delete
because it can be very expensive especially if S is long
and contains many Delimiters.
}

// Here is the obverse, joining a stringlist into a single string delimited by the
// passed delimiter
function Join(Delimiter: string; Strings: TStrings): String;
var
  i: Integer;
begin
  Result := '';
  for i := 0 to Strings.Count-1 do
    case i of
    0: Result := Result + Strings[i];
    else Result := Result + Delimiter + Strings[i];
    end;
end;
0
 
Eddie ShipmanAll-around developerCommented:
Usage:

Split('6003|Andy Smith|2004-11-23 16:41:00', '|', ListBox1.Items);

Returns in ListBox1.Items:

6003
Andy Smith
2004-11-23 16:41:00
0
 
RatjeCommented:
Add this to what Workshop_Alex suggested and spaces are no longer considered delimeters.

Replace
 for i := 0 to Pred( StrNum.count ) do begin
    StrLine.DelimitedText := StrNum[ i ];
with

for i := 0 to Pred( StrNum.count ) do begin
sString := StrNum[ i ];
sString := StrinReplace(sString, '|', '"|"',[rfReplaceAll]) ;
sString := '"' + sString + '"' ;
//sString now contains : "6003"|"Andy Smith"|"2004-11-23 16:41:00"
StrLine.DelimitedText := sString ;
0
 
aikimarkCommented:
Another approach would be to use the StringReplace() function to change all spaces to some character value not otherwise found in your string before performing the delimited string parse.  This are usually characters between ASCII 128 and 255.

After the parse, you could automatically or conditionally replace the replacement character back to a space.

I've done this when storing and retrieving text that might have special punctuation characters, such as quotes and apostrophes, that would mess up my delimited-text I/O routines.
0
 
lloydie-tAuthor Commented:
If yo dont mind I have split the points between Workshop_alex and Ratje
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.

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