sql-info.de
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