Solved

Import a txt file into a firebird table

Posted on 2008-06-10
12
2,064 Views
Last Modified: 2012-08-14
Hi

I have an application in Delphi 7 and i use firebird database.I want to help me about how can i insert all the data of a txt file into a table.Here is an example about the txt and table structure.

txt file :

6- 9-2008,1,2,2086,0,634
6- 9-2008,2,2,2002,0,2402
6- 9-2008,3,4,7150,0,3575
6- 9-2008,5,1,500,0,173
6- 9-2008,97,2,4000,0,2760

table structure & fields name:

date  ---  value1 --- value2 --- value3 --- value4 --- value5

Thanks in advance!
0
Comment
Question by:alex_code
12 Comments
 
LVL 37

Accepted Solution

by:
Geert Gruwez earned 250 total points
ID: 21749751
i assume each line is to be a record



procedure ReadTextToDB(FileName: string; TableName: string; DataBaseName: string);
var 
  List, Line:  TStrings;
  I: Integer;
  Qry: TQuery;
begin
  List := TStringList.Create;
  try
    Line := TStringList.Create;
    try
      List.LoadFromFile(FileName);
      Qry := TQuery.Create;
      try
        Qry.DatabaseName := DatabaseName;
        Qry.SQL.Text :=
          'INSERT INTO ' + TableName + ' (FIELD_1, FIELD_2, FIELD_3, FIELD_4, FIELD_5, FIELD_6) '+
          'VALUES (:F1, :F2, :F3, :F4, :F5, :F6);
        Qry.Prepare;
        for I := 0 to List.Count - 1 do 
        begin
          Line.CommaText := List[I];
          for J := 0 to 5 do
            if J = 0 then 
              Qry.ParamByName(Format('F%d', [J+1])).AsDateTime := StrToDateTime(Line[J])
            else 
              Qry.ParamByName(Format('F%d', [J+1])).AsString := Line[J];
          Qry.ExecSQL;
        end;
      finally
        FreeAndNil(Qry);
      end;
    finally
      FreeAndNil(Line);
    end;
  finally
    FreeAndNil(List);
  end;
end;

Open in new window

0
 
LVL 9

Author Comment

by:alex_code
ID: 21749932
Hi Geert_Gruwez

Thanks for the post but that does not work for me i said that i use a firebird database and the example you gave me is not so clear for me about what i have to do.
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 21750368
works for any database which BDE can connect to
is just a general way of doing things
create a BDE alias with DatabaseName and connect it to your Firebird database.
then use proc
i admit, it may need some tweaks here and there
i just used this editor to supply the code.
no delphi here atm


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!

 
LVL 21

Assisted Solution

by:developmentguru
developmentguru earned 250 total points
ID: 21751791
 The solution that Geert_Gruwez gave you is good.  You may need to check that each line actually contains 5 items or you will get an "list index out of bounds" error.  Instead of using paramter replacement as he is (The firebird components you are using may not support that) you can build the query more "by hand".  The goal is to create an insert statement to get the data into the database.  If the date format of firebird is different then you may need to spend more time trying to make a query where the date format matches.

  One other key piece of information... Willl the text file ever contain information that may have been entered in the past?  If so, do you want to input it again, or overwrite it?  It looks to me like the first 2 fields could be used as a unique id (date and entry number for date).  If this is the case then you would first query to see if such a record exists.  If it does, you can ignore it or update it.  Then you would need to generate an update query instead of insert.

  The concept he gave you is valid, spend some time with it and let us know what specifically does not work for you, what components you are using to talk to the firebird database, etc.
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 21753300
The alternative is to use the file as an external table

http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_native_external
0
 
LVL 9

Author Comment

by:alex_code
ID: 21759082
So my friends look what i have done until now and please help me to finish it!!!!

Here some data of txt file

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

1;9;6000;0;6000;0
2;8;7500;0;9000;0
3;7;7995;0;3998;0
4;6;10000;0;3000;0
5;7;10493;0;2099;0
6;4;10995;0;13194;0

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Here the code


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

procedure TForm1.Button4Click(Sender: TObject);
var
sl   : tstringlist;
i,j  :integer;
s,ss :string;
begin
sl := TStringList.Create;
sl.LoadFromFile('report.txt');
 For I := 0 to SL.Count - 1 do
          begin
            s := sl[i];

              If pos(';',s) > 0 then
              begin
                ss := Trim(copy(s,1,pos(';',s)-1));
                try

                   //read and seperate txt file line values i seperated by ; character
                    label7.Caption:=ss;  //variable that must keep value1
                    label8.Caption:=ss;  //variable that must keep value2
                    label9.Caption:=ss;  //variable that must keep value3
                    label10.Caption:=ss; //variable that must keep value4
                    label11.Caption:=ss; //variable that must keep value5
                    label12.Caption:=ss; //variable that must keep value6
                    //label13.Caption:=SS;

              ibquery3.SQL.Clear;
              ibquery3.SQL.Append('insert into reports (name1,name2,name3,name4,name5,name6) values (value1,value2,value3,value4,value5,value6)');
              ibquery3.ExecSQL;
              ibtransaction1.Commit;

                except
                raise;

               end;
              end;
            end;

end;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

How can i handle those values and seperated in several variables so when i go to store them into the table to store the right values in each record.

Thanks a lot.
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 21759542
didn't you see the line ???

Line.CommaText := List[I];

This separates you comma separated list into the separate values ...
you can then get to the separate values as
Label7.Caption := Line[0] ;
Label8.Caption := Line[1] ;
Label9.Caption := Line[2] ;
Label10.Caption := Line[3] ;

and the SQL.Append should be
ibquery3.SQL.Append('insert into reports (name1,name2,name3,name4,name5,name6) values (:value1,:value2,:value3,:value4,:value5,:value6)');
for I := 1 to 6 do
  ibQuery3.ParamByName('value'+  IntToStr(I)).AsString := Line[I-1];
ibQuery3.ExecSQL

a small question :
how long have you been coding in Delphi
or
how long have you been using Delphi



0
 
LVL 9

Author Comment

by:alex_code
ID: 21759713
First of all I develop apps since 2006.Why are you asking?

Second: you said ---> didn't you see the line ???

                                  Line.CommaText := List[I];

This line it is in your code sample, the code i gave in my previous post does not contain any line about    this, i used another code sample and i modified.

 So i was asked from all of you to help me how can i modify this code to do what i want to do.

Third:

You said to seperate the values like this ---> Label7.Caption := Line[0] ;

this is static i don't know how many lines will have each time the txt file so this array must be a dynamically value.
     
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 21759962
aha, now we are getting some where
aha, uhm no not really...

if the lines in the text file change, i mean the number of columns,
how do you know which item must go in which column ?
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 21760046
consistency helps too ...

in your question you posted data like:
txt file :

6- 9-2008,1,2,2086,0,634
6- 9-2008,2,2,2002,0,2402
6- 9-2008,3,4,7150,0,3575
6- 9-2008,5,1,500,0,173
6- 9-2008,97,2,4000,0,2760

in a response you posted:

1;9;6000;0;6000;0
2;8;7500;0;9000;0
3;7;7995;0;3998;0
4;6;10000;0;3000;0
5;7;10493;0;2099;0
6;4;10995;0;13194;0

to get this separated you use:
Line.Delimter := ';';
Line.DelimitedText := List[I];

Works too if the delimiter is ','
Line.Delimter := ',';
Line.DelimitedText := List[I];

if you want dynamic columns then :
var Temp: string;
Temp := 'INSERT INTO TABLE (';
for I := 0 to Line.Count-1 do
  Temp := Temp + Format('NAME%d, ', [I+1]);
Delete(Temp, Length(Temp)-1, 2);
Temp := Temp + ') VALUES (';
for I := 0 to Line.Count-1 do
  Temp := Temp + Format(':VALUE%d, ', [I+1]);
Delete(Temp, Length(Temp)-1,2);
Temp := Temp + ')';
ibQuery3.SQL.Text := Temp;
ibQuery3.SQL.Prepare;
For I := 0 to Line.Count-1 do
  ibQuery3.ParamByName(Format('VALUE%d', [I+1])).AsString := Line[I];
ibQuery3.ExecSQL;

last but not least, do you want to display data of several lines or just one record ?
several lines --> use grid instead of captions
one line --> use grid too instead of captions

for I := 0 to Line.Count-1 do
  grid.Cell[X, I] := Line[I];
 


0

Featured Post

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!

Question has a verified solution.

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

Suggested Solutions

Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

679 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