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,
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.