Re: Importing lines of variable length from ASCII

On 26 February 2010 09:06, Louis Becker <Louis.Becker@leo.na> wrote:
> Hi Andre
>
> Luckily have installed cygwin on my WindowsXP box, so I should be able
> to execute your suggested command. Is there not a way for PostGres to
> absorb this? I prefer not to pre-process all the time, as this would be
> a daily task. If I am just able to adjust the copy query, that would be
> preferred. Very few of my colleagues are Unix/Linux capable and most are
> command line phobic. If I am able to save the query/command that would
> be much easier.
>
> Louis
>

It appears that you will either have to fix whatever is generating the
file to have non-variable columns, or import the whole file into a
table in PostgreSQL without delimiters, and process the table to
insert into another table, sort of like this:

CREATE TABLE initial_file_import
(
    data text NOT NULL
);

/* Do your import into initial_file_import here but for now, we'll
just do some inserts */
INSERT INTO initial_file_import VALUES ('stuff|4|more
stuff|2'),('things|2|yay'),('hello|9');

CREATE TABLE file_import
(
    field1 text NOT NULL,
    field2 int NOT NULL,
    field3 text NULL,
    field4 int NULL
);

CREATE FUNCTION split_file_records() RETURNS VOID AS $$
DECLARE importline TEXT;
BEGIN
    FOR importline IN SELECT data FROM initial_file_import LOOP
        INSERT INTO file_import VALUES (
            split_part(importline, '|', 1),
            split_part(importline, '|', 2)::int,
            split_part(importline, '|', 3),
            ('0'||split_part(importline, '|', 4))::int);
END LOOP;
END;
$$
LANGUAGE plpgsql;

SELECT split_file_records();

SELECT * FROM file_import;

Note that I prefixed the numeric field which can be absent with a 0 to
prevent issues with blank strings casting to integers.

Thom