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_
