Import csv file beginning on specific line number

Question

I want to import a csv file into a table beginning on line 9 of the csv file. How do I specify this condition in postgresql?

The first 8 lines have a bunch of irrelevant text describing the data below. This is a screenshot of the file imported into Excel.

And this is the table in my db I am trying to insert the data into.

CREATE TABLE trader.weather
(
station text NOT NULL,
"timestamp" timestamp with time zone NOT NULL,
temp numeric(6,2),
wind numeric(6,2)
)

score 2 · Answer 1 · answered Jan 12 '18 at 22:00

It can't be done on PostgreSQL, you should do it with an external tool or process before postgres.

According to the manual, the only processes you can do to a CSV are mostly QUOTE or NULL related:

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | STDIN }
    [ [ WITH ]
          [ BINARY ]
          [ OIDS ]
          [ DELIMITER [ AS ] 'delimiter' ]
          [ NULL [ AS ] 'null string' ]
          [ CSV [ HEADER ]
                [ QUOTE [ AS ] 'quote' ]
                [ ESCAPE [ AS ] 'escape' ]
                [ FORCE NOT NULL column_name [, ...] ] ] ]

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | STDOUT }
    [ [ WITH ]
          [ BINARY ]
          [ OIDS ]
          [ DELIMITER [ AS ] 'delimiter' ]
          [ NULL [ AS ] 'null string' ]
          [ CSV [ HEADER ]
                [ QUOTE [ AS ] 'quote' ]
                [ ESCAPE [ AS ] 'escape' ]
                [ FORCE QUOTE { column_name [, ...] | * } ] ] ]

There are many ways to alter a CSV automatically before using it in PostgreSQL, you should check other options.

Erwin Brandstetter · Answer 2 · 2018-01-19T14:41:07.057

1

It can be done with Postgres, just not with COPY directly.

Use a temporary staging table like this:

CREATE TEMP TABLE target_tmp AS
TABLE target_tbl LIMIT 0;  -- create temp table with same columns as target table

COPY target_tmp FROM '/absolute/path/to/file' (FORMAT csv);

INSERT INTO target_tbl
TABLE  target_tmp 
OFFSET 8;  -- start with line 9

DROP TABLE target_tmp; -- optional, else it's dropped at end of session automatically

The skipped rows must be valid, too.

Obviously, this is more expensive - which should not matter much with small to medium tables. Matters with big tables. Then you really should trim the surplus rows in the input file before importing.

Make sure your temp_buffers setting is big enough to hold the temp table to minimize the performance penalty.

Related (with instructions for \copy without superuser privileges):

How to update selected rows with values from a CSV file in Postgres?

edited Jan 19 '18 at 14:41

answered Jan 12 '18 at 23:12

Erwin Brandstetter

605,456
145
1,078
1,228

I have added some detail about my data set into the question. Thanks. – otterdog2000 Jan 18 '18 at 19:39
I am also getting ERROR: must be superuser to COPY to or from a file – otterdog2000 Jan 18 '18 at 21:02
1

@otterdog2000: You can use the psql meta-command `\copy`. I added detailed instructions to the linked answer. – Erwin Brandstetter Jan 19 '18 at 14:41
I get this error when I replace COPY with \copy...........ERROR: syntax error at or near "\" LINE 4: \copy weather_temp FROM /D:/Google Drive/Work/Fortress/mesow... – otterdog2000 Jan 23 '18 at 19:12
basically, I don't understand how to implement \copy command. BTW, my db is in the cloud. the files i'm trying to import are stored on my local machine. – otterdog2000 Jan 23 '18 at 19:23
and i'm running the sql queries on pgAdmin III – otterdog2000 Jan 23 '18 at 19:40
1

@otterdog2000: `\copy` is a psql meta-command, not an SQL command You cannot run from the pgAdmin SQL editor, only from psql (the default interactive terminal). The linked answer is explicit about this. – Erwin Brandstetter Jan 23 '18 at 23:34
I didn't have the psql sql shell installed. Hence my confusion. Thanks. – otterdog2000 Jan 24 '18 at 15:30

Import csv file beginning on specific line number

2 Answers2