Search Postgresql Archives

Re: create a script which imports csv data

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Robert Buckley wrote:
I have to create a script which imports csv data into postgresql
The csv data is automatically created from an external database
so I have no influence over which columns etc are downloaded.

How can I best create a table for the import?


This is what I do:

1) I have a table defined for import which reflects the CSV structure of the data to be imported, i.e., since I know what the fields are on each line of the CSV, this template table has columns defined to accommodate each known field. This table never actually gets data written to it.

2) To import data, my script creates a temporary import table LIKE the import template table.

3) The script then transfers and transform the data from the temporary import table to another permanent table that has the structure, including a primary key, that is more useful for my purposes. (It omits some of the columns which I do not really need from the CSV, uses a different name for one column, and adds some reference information. You could do calculations here as well.)

4) The temporary import table is deleted at the end of the import session.

Here is a sanitized (names changed to protect the innocent) version of the script (the script parameter '$1' is the name of the CSV file):

#!/bin/bash

# This script imports a CSV file of transactions from Discover.

#!/bin/bash

# This script imports a CSV file of transactions from Discover.

psql mydb <<-_END-OF-SCRIPT_
CREATE LOCAL TEMPORARY TABLE i (LIKE my_financial_schema.import_discover_card);
COPY i
(transaction_date, post_date, description, amount, category, share, net, type, paid_date)
FROM '$1'
WITH (FORMAT CSV, DELIMITER ',', QUOTE '"');
INSERT INTO my_financial_schema.my_permanent_record_table(
        transaction_date,
        paid_date,
        reference,
        category,
        amount,
        description
        )
    SELECT
      transaction_date,
      paid_date,
      'Discover Card',
      type,
      net,
      description
      FROM i;
DROP TABLE i;
_END-OF-SCRIPT_


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux