Solved

Import a txt file into a firebird table

Posted on 2008-06-10
12
1,979 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

863 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now