Solved

Import a txt file into a firebird table

Posted on 2008-06-10
12
1,942 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 36

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 36

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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 36

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 36

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 36

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

746 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

14 Experts available now in Live!

Get 1:1 Help Now