PostGreSQL CSV import line separator

Question

I am trying to export data from MySQL table and import that data into the PgSQl database.

EXPORT Script from MySQL

select column_names from from table_name LIMIT 2
INTO OUTFILE 'E:/CSV_files/record_main.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY ';'

PgSQL IMPORT script

COPY record_main("column_names ") FROM 'E:/CSV_files/record_main.csv' 
WITH DELIMITER ',' NULL '\N'   ESCAPE ';' CSV

The above code is working fine for me when i am trying to export and import the CSV.

But while i am increasing the selection limit 1 to 2 or any , It causes error.

ERROR: extra data after last expected column CONTEXT: COPY record_main, line 1: >""12635","55025","1","ASCS","P","5320700","2015-01-01 >00:00:00",\N,\N,\N,\N,\N,\N,"DEED",\N,\N,\N,\N,..."

Then opened the CSV file and i manually separated the one row of data in to two row of data.

And i removed the semicolon from the end of each line.

This time it is working fine. I think error in the line separator. My question is how can i say to the PgSQl to the line separator is this. like i given the DELIMITER

score 2 · Accepted Answer · edited Aug 06 '19 at 08:51

2

As far as I know, postgresql's copy statement expects the rows to be separated by new line character(s):

COPY FROM can handle lines ending with newlines, carriage returns, or carriage return/newlines.

So, you have to adjust the mysql export to use new line character(s) as line separator or you need to pre-process the dump to replace the ; characters with new line.

edited Aug 06 '19 at 08:51

klin

86,071
10
131
159

answered Aug 26 '17 at 10:11

Shadow

30,859
10
44
56

score 2 · Answer 2 · answered Aug 26 '17 at 10:11

From this SO question the syntax you should be using for COPY is:

COPY record_main FROM 'E:/CSV_files/record_main.csv' DELIMITERS ',' CSV;

As you can see, this is different from the command you were trying to run. But COPY runs on the database server and requires a root account. If you don't have one, you could also try using \copy:

\copy record_main(col1, col2, ...) FROM 'E:/CSV_files/record_main.csv'
    DELIMITER ',' CSV

Update:

@shadow correctly pointed out that you need to export your MySQL data into a CSV file whose lines are separated by a line separator, not semicolon. But your Postgres copy syntax looks off, so maybe this answer will still help you.

use no quotes on \copy filenames. – Jasen Aug 26 '17 at 10:59 — Jasen, Aug 26 '17 at 10:59

score 0 · Answer 3 · answered Aug 26 '17 at 10:32

Thanks guys,

I am doing wrong here by giving wrong line separator while creating the CSV from MySQL.

correct answer

INTO OUTFILE 'E:/CSV_files/record_main-new.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'

There is no need to specify the line separator in the PostgreSQL automatically or default "\n"

From CSV to PostgreSQL import.

FROM 'E:/CSV_files/record_main-new.csv' 
WITH DELIMITER ',' NULL '\N'  CSV

PostGreSQL CSV import line separator

3 Answers3