Well,
This seems like a convoluted way to import data from a flat file into a
database.

What I ended up doing is importing data from a flat file into my
PostgreSQL database. Then, using postgres pg_dump utility, I exported
data from the postgres database in a file in a form of INSERT
statements. Finally, I created my table in my FB database and ran the
INSERTs script in there.
Inserting 1,500,000 rows took 30mins., which is not so bad.
I have another table, which contains 6,000,000 rows, so I guess I will
be having a long lunch today :)
I am not sure if I have my write-back cache enabled or not. I will make
sure to disable it before I do the next insert.
Is there a way to find out whether the async write is enabled or
disabled?

Thanks.

Oleg

-----Original Message-----
From: Milan Babuskov [mailto:albis@...]
Sent: Tuesday, September 30, 2003 10:14 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Import data from flat files
Importance: Low


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:
>
> CREATE TABLE Orders
> 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
> 2|78002|O|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,
> 7|39136|O|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
http://fbexport.sourceforge.net




To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com



Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/

*************************************

This e-mail may contain privileged or confidential material intended for the named recipient only.
If you are not the named recipient, delete this message and all attachments.
Unauthorized reviewing, copying, printing, disclosing, or otherwise using information in this e-mail is prohibited.
We reserve the right to monitor e-mail sent through our network.

*************************************