Solved

Import a txt file into a firebird table

Posted on 2008-06-10
12
2,188 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 38

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
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 38

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
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 38

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
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 38

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 38

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses
Course of the Month4 days, 7 hours left to enroll

635 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