February 3, 2014
Firebird Blog | Importing CSV files to Firebird
Let's assume you have a CSV file which looks like this:
$ head -5 /tmp/prod.csv 1,14,ACADEMY ACADEMY,PENELOPE GUINESS,25.99,0,1976 2,6,ACADEMY ACE,EWAN RICKMAN,20.99,0,6289 3,6,ACADEMY ADAPTATION,VIVIEN KAHN,28.99,0,7173 4,3,ACADEMY AFFAIR,ALAN MARX,14.99,0,8042 5,3,ACADEMY AFRICAN,CARRIE HANNAH,11.99,1,2183
(from the Dell DVD store) and you want to import it to Firebird, which has no built-in CSV import mechanism. One possibility is to take a recent PostgreSQL version (9.3 or later, example shown here uses the soft, pink bleeding edge) and do this:
psql (9.4devel) Type "help" for help. postgres=# CREATE EXTENSION file_fdw; CREATE EXTENSION postgres=# CREATE SERVER file_fdw FOREIGN DATA WRAPPER file_fdw; CREATE SERVER postgres=# CREATE FOREIGN TABLE products_csv ( prod_id INT NOT NULL, category INT NOT NULL, title VARCHAR(100) NOT NULL, actor VARCHAR(100) NOT NULL, price NUMERIC(7,2) NOT NULL, special SMALLINT NOT NULL, common_prod_id SMALLINT NOT NULL ) SERVER file_fdw OPTIONS ( filename '/tmp/prod.csv', format 'csv' ); CREATE FOREIGN TABLE postgres=# SELECT count(*) FROM products_csv ; count ------- 10000 (1 row)
then do this:
postgres=# CREATE EXTENSION firebird_fdw; CREATE EXTENSION postgres=# CREATE SERVER firebird_fdw FOREIGN DATA WRAPPER firebird_fdw OPTIONS ( address 'localhost', database '/srv/firebird/testdb' ); CREATE SERVER postgres=# CREATE USER MAPPING FOR CURRENT_USER SERVER firebird_fdw OPTIONS(username 'sysdba',password 'masterke'); CREATE USER MAPPING postgres=# CREATE FOREIGN TABLE products ( prod_id INT NOT NULL, category INT NOT NULL, title VARCHAR(100) NOT NULL, actor VARCHAR(100) NOT NULL, price NUMERIC(7,2) NOT NULL, special SMALLINT NOT NULL, common_prod_id SMALLINT NOT NULL ) SERVER firebird_fdw; CREATE FOREIGN TABLE
and then do this:
postgres=# INSERT INTO products SELECT * FROM products_csv; INSERT 0 10000 postgres=# SELECT count(*) FROM products; count ------- 10000 (1 row)
and look ma, no native PostgreSQL tables at all:
postgres=# \dn List of schemas Name | Owner --------+--------- public | barwick (1 row) postgres=# \dt No relations found. postgres=# \det List of foreign tables Schema | Table | Server --------+--------------+-------------- public | products | firebird_fdw public | products_csv | file_fdw (2 rows)
Quite why you would want to do this is another question, but the point is - It Can Be Done.
And in the next installment of Fun With Foreign Data Wrappers, I'll be showing you how you can import MySQL tables into Oracle (once I've cleaned up after being pelted with rotten fruit).
Posted at 3:34 PM