How can I grab sections of a text file ?

John86a
John86a used Ask the Experts™
on
Hi, supposing I generate a text file with the following template:

SOME  
MULTI
LINE
CONTENT
£
ANOTHER
MULTI
LINE
CONTENT
£
YET
ANOTHER
£
AND
ONE
MORE

Open in new window



Seeing that the content is separated by Libra sign (since it's unique and unlikely to be used in SQL), how can I programatically grab each section and  paste it into sql queries?
I.e.:
query.SQL.ADD(Section1); 
query.ExecSQL;
query.SQL.Clear;
Query.SQL.ADD(Section2);
query.ExecSQL;
query.SQL.Clear;

and so on..

Open in new window


Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Emmanuel PASQUIERFreelance Project Manager
Top Expert 2010
Commented:
let's suppose we regroup those functions in a DataModule as it will be the most convenient way of doing such a thing, with all DB components, and specially a query component called "MultiQuery"

I will not manage the cases where the text in a section is empty or full of spaces/line return for example, that is up to you to add all the improvements you see fit

Procedure TDataModule.MultiSQLExecFile(FileName:String); 
Var
 L:TStringList;
begin
 L:=TStringList.Create;
 try
  L.LoadFromFile(FileName);
  MultiSQLExecStrList(L);
 finally
  L.Free;
 end;
end;

Procedure TDataModule.MultiSQLExecStrList(StrList:TStrings); 
var
 i:integer;
begin
 MultiQuery.SQL.Clear; 
 i:=0;
 while i<StrList.Count do
  begin
   if StrList[i]='£' Then 
    begin
     MultiQuery.ExecSQL;
     MultiQuery.SQL.Clear;
    end Else MultiQuery.SQL.Add(StrList[i]);
   inc(i); 
  end;
 // execute the last one if the file do not terminate with '£'
 if MultiQuery.SQL.Count>0 Then MultiQuery.ExecSQL;
end;

Open in new window

Commented:
glad to see you again epasquier =)
Top Expert 2011
Commented:
You can load the text as Delimited Text and set the pound sign "£" as Delimiter Char which will enable you to have the SQL Statements in StringList items, all you have to do thereafter is to loop and execute each item:
procedure TForm1.Button1Click(Sender: TObject);
var
  Strlst, Strtxt:TStrings;  // Strlst will hold the SQL lines & Strtxt will load the txt file
  i:integer;
begin
  strlst:=TStringlist.Create;
  strtxt:=TStringlist.Create;
  try
    strtxt.LoadFromFile('SQLText.txt');   //'SOME MULTI LINE CONTENT £ ANOTHER MULTI LINE CONTENT £ YET ANOTHER £ AND ONE MORE';
    strlst.Delimiter := '£';
    Strlst.StrictDelimiter:= true;
    strlst.DelimitedText := strtxt.Text;
    for i:= 0 to strlst.Count-1 do
      begin
        //showmessage(strlst[i]);  // to test if you see each SQL line before executed
        query.SQL.Clear;
        query.SQL.ADD(strlst[i]);
        query.ExecSQL;
      end;
  Finally
    Strtxt.Free;
    Strlst.Free;
  end;
end;

Open in new window

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.

Top Expert 2011
Commented:
BTW you can eliminate one of the Stringlists:
procedure TForm1.Button1Click(Sender: TObject);
var
  Strlst:TStrings;  // Strlst will hold the SQL lines
  i:integer;
begin
  strlst:=TStringlist.Create;
  try
    strlst.LoadFromFile('SQLText.txt');   //'SOME MULTI LINE CONTENT £ ANOTHER MULTI LINE CONTENT £ YET ANOTHER £ AND ONE MORE';
    strlst.Delimiter := '£';
    Strlst.StrictDelimiter:= true;
    strlst.DelimitedText := strlst.Text;
    for i:= 0 to strlst.Count-1 do
      begin
        //showmessage(Trim(strlst[i]));  // to test if you see each SQL line before executed
        query.SQL.Clear;
        query.SQL.ADD(strlst[i]);
        query.ExecSQL;
      end;
  Finally
    Strlst.Free;
  end;
end;

Open in new window

Top Expert 2014

Commented:
Until this question, I'd never thought that there could be confusion between the pound keyboard character and the British currency pound symbol.

Makes my head pound. (I'm sure there's a symbol for that too)

=======
@John86a

The British currency symbol may be rare, but it is used frequently in some parts of the world.

Author

Commented:
@aikimark I'll have you know that I've lived in London for quite a few years, and yes, I'm well acquainted with the currency pound. However, you should also consider that it's not called pound everywhere in the world, and that your comment was nothing but futile and wholly derailed from the topic objective.

Some countries - like the one where I'm from, calls the British currency 'Libra Sterling'. I guess you wouldn't know that, since you're evidently caved in.

Author

Commented:
Thanks guys.
Top Expert 2014

Commented:
@John86a

>>...like the one where I'm from...
You registered with EE from a Brazilian IP address.  Is that the country that uses Libra instead of Pound to refer to UK currency?

>>...caved in
With the wealth of information available on the 'Web, I'm not sure I could classify myself as caved in even if I never left the house.  My posted comment was referring to your "unique and unlikely to be used in SQL" assertion.  I had no idea what country you were in or from when I wrote that.

The most common reference in the world, certainly in the financial world and in foreign-exchange markets, seems to be Pound Sterling.  My comment was meant to contribute to the resiliency of your application/database, not to denigrate you in any way.

You're welcome.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial