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
lloydie-tAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wim ten BrinkSelf-employed developerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 BrinkSelf-employed developerCommented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.