Subject Re: [firebird-support] Import data from flat files
Author Milan Babuskov
Oleg Lebedev wrote:
> Actually, when I said that only 90% or rows were imported, I was basing
> this on the count(*) query run against the external table.
> I created the external table as follows:
> EXTERNAL 'orders.tbl'
> (
> o_orderkey int NOT NULL ,
> o_custkey int NOT NULL ,
> o_orderstatus char (1) NOT NULL ,
> o_totalprice decimal(18, 0) NOT NULL ,
> o_orderdate timestamp NOT NULL ,
> o_orderpriority char (15) NOT NULL ,
> o_clerk char (15) NOT NULL ,
> o_shippriority int NOT NULL ,
> o_comment varchar (79) NOT NULL
> );

Ok, first I suggest you only use chars for external table so you can get
the exact width. For real table use the right datatypes like you did
here. When you copy data to a real table, the Firebird will do
conversions, and you can easily spot any errors then.

> Here are a couple of lines from my orders.tbl file:
> 1|36901|O|173665.47|1996-01-02|5-LOW|Clerk#000000951|0|blithely final
> 2|78002|O|46929.18|1996-12-01|1-URGENT|Clerk#000000880|0|quickly regular
> 3|123314|F|193846.25|1993-10-14|5-LOW|Clerk#000000955|0|deposits
> 4|136777|O|32151.78|1995-10-11|5-LOW|Clerk#000000124|0|final requests
> 5|44485|F|144659.20|1994-07-30|5-LOW|Clerk#000000925|0|even deposits
> 6|55624|F|58749.59|1992-02-21|4-NOT
> 7|39136|O|252004.18|1996-01-10|2-HIGH|Clerk#000000470|0|ironic, regular

Data must be fixed length. It means that each column must have exactly
the same number of characters in eash row, and the extra should be
padded with spaces. The data you did manage to import is probably also
bad. Select first 10 or 20 records to see that. You have to calculate
maximum column length for each column, and create external table like that.

Also, when defining the external table, you need to leave one or two
bytes for newline character (depending of the type of the file). Since
you're on Linux, it probably only has newline (\n) at end of each row.

Milan Babuskov