Solved

Import a txt file into a firebird table

Posted on 2008-06-10
12
2,007 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Printing problem 2 95
find a node in VST 2 69
Performance of SQL statement 37 112
Delphi Seattle StructureView color 1 5
A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

803 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