CODEWITHSUNDEEP
postgresql
MAK
MAK

Reputation: 7270

PostgreSQL 9.5: Skip first two lines in the text file

I have the text file to import with the following format:

columA    | columnB    |    columnC
-----------------------------------------
1     |     A          |    XYZ
2     |     B          |    XZ
3     |     C          |    YZ

I can skip first line by using:

WITH CSV HEADER;

in copy command, but got stuck while skipping second line.

Upvotes: 2

Views: 3926

Answers (2)

Nick Barnes
Nick Barnes

Reputation: 21385

If you're using COPY FROM 'filename', you could instead use COPY FROM PROGRAM to invoke some shell command which removes the header from the file and returns the rest.

In Windows:

COPY t FROM PROGRAM 'more +2 "C:\Path\To\File.txt"'

In Linux:

COPY t FROM PROGRAM 'tail -n +3 /path/to/file.txt'

If you're trying to send a local file to a remote server, you can do something similar through psql, e.g.:

tail -n +3 file.txt | psql -c 'COPY t FROM STDIN'

Upvotes: 6

Patrick
Patrick

Reputation: 32384

The COPY command can only skip the first line. The easiest solution would be to manually remove the second line before importing but if that is not possible, then you have to use a "dirty" trick.

You create a table that has a single column of varchar type and import the text file into that table. After import you run a PL/pgSQL function to read all the rows in the table (except the header rows, obviously) and extract the information you want to insert in the destination table with, for instance, the regexp_matches() or regexp_split_to_array() function. You can also automate the whole process by using an after insert trigger on the import table, if you have to import many files with the same issue.

Upvotes: 0

Related Questions