mysql cannot import csv lines terminated with \r\n\r\n using load data local

Question

I am trying to import the CSV provided by a service that should give me cities database.

The way format of the cities in csv format is:

CC_FIPS FULL_NAME_ND
AN  Aixas

AN  Aixirivall

AN  Aixovall

AN  Andorra la Vella

AN  Ansalonga

So I came up with the following mysql query:

LOAD DATA LOCAL
INFILE 'GEODATASOURCE-CITIES-FREE.TXT'
INTO TABLE `city`
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(cc_fips,full_name_nd);

They all get imported but the entries under the column of full_name_nd has a whitespace after. Since as you can see there's extra \r\n at the end of each line.

Which I can confirm with cat short.txt | od -c

0000000   C   C   _   F   I   P   S  \t   F   U   L   L   _   N   A   M
0000020   E   _   N   D  \r  \n   A   N  \t   A   i   x   a   s  \r  \n
0000040  \r  \n   A   N  \t   A   i   x   i   r   i   v   a   l   l  \r
0000060  \n  \r  \n   A   N  \t   A   i   x   o   v   a   l   l  \r  \n
0000100  \r  \n   A   N  \t   A   n   d   o   r   r   a       l   a
0000120   V   e   l   l   a  \r  \n  \r  \n   A   N  \t   A   n   s   a
0000140   l   o   n   g   a  \r  \n  \r  \n   A   N  \t   A   n   y   o
0000160   s  \r  \n  \r  \n   A   N  \t   A   r   a   n   s  \r  \n  \r
0000200  \n   A   N  \t   A   r   i   n   s   a   l  \r  \n  \r  \n   A
0000220   N  \t   A   u   b   i   n   y   a  \r  \n  \r  \n   A   N  \t

So modifying the query, using \r\n\r\n:

LOAD DATA LOCAL
INFILE 'GEODATASOURCE-CITIES-FREE.TXT'
INTO TABLE `city`
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n\r\n'
IGNORE 1 LINES
(cc_fips,full_name_nd);

But that query won't work :(

Query OK, 0 rows affected (0.40 sec)
Records: 0  Deleted: 0  Skipped: 0  Warnings: 0

I even tried with

LOAD DATA LOCAL
INFILE 'GEODATASOURCE-CITIES-FREE.TXT'
INTO TABLE `city`
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(cc_fips,@col_with_spaces)
SET full_name_nd = TRIM(@col_with_spaces);

But apparently, TRIM() doesn't work on \r, \n or other special whitespaces.

Hope somebody can help.

Regards,

Hendrik Prinsloo · Accepted Answer · 2018-10-23 14:43:10Z

1

Not sure if this will work as I haven't tested it, but have you tried replacing the special characters. Something like this:

LOAD DATA LOCAL
INFILE 'GEODATASOURCE-CITIES-FREE.TXT'
INTO TABLE `city`
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(cc_fips,@col_with_spaces)
SET full_name_nd = TRIM(REPLACE(@col_with_spaces, '\r\n', ''));

If it doesn't work, try to use '\n' instead of '\r\n'.

answered Oct 23, 2018 at 14:43

Hendrik Prinsloo

3291 silver badge11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hendrik Prinsloo Over a year ago

Thanks for checking.

Hendrik Prinsloo Over a year ago

@TheWolf, text files created on DOS/Windows machines have different line endings than files created on Unix/Linux. The file you got from GeoDataSource was probably generated in a DOS environment with "\r\n". Therefore, running dos2unix on it resolved the line endings issue. You can also do it the other way with unix2dos.

arvil · Accepted Answer · 2018-10-24 09:41:19Z

1

SOLVED. using dos2unix tool on the 'GEODATASOURCE-CITIES-FREE.TXT' first before using the SQL query I posted in my question solved it. Looking forward to anyone who can explain why converting it to unix line endings solved it.

answered Oct 24, 2018 at 9:41

arvil

9321 gold badge15 silver badges33 bronze badges

Collectives™ on Stack Overflow

mysql cannot import csv lines terminated with \r\n\r\n using load data local

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related