When i first wanted to apply the LOAD DATA INFILE, my server said:
|
1 2 3 |
Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement |
So i asked the server where i may put the import file:
|
1 2 3 4 5 6 7 8 |
mysql> SHOW VARIABLES LIKE "secure_file_priv"; +------------------+-----------------------+ | Variable_name | Value | +------------------+-----------------------+ | secure_file_priv | /var/lib/mysql-files/ | +------------------+-----------------------+ 1 row in set (0,01 sec) mysql> |
I put the csv file to import in the directory my server asked me to.
My csv looked like this: (Tab delimited, 10 columns, last one was not reliably there.)
|
1 2 3 4 |
D AD100 Canillo 42.5833 1.6667 6 AD AD200 Encamp 42.5333 1.6333 6 AD AD300 Ordino 42.6 1.55 6 AD AD400 La Massana 42.5667 1.4833 6 |
To insert i fired the following SQL:
|
1 2 3 4 5 6 7 8 9 10 |
LOAD DATA INFILE "/var/lib/mysql-files/allCountries.txt" INTO TABLE datacoll.location_zip_allCountry COLUMNS TERMINATED BY '\t' (countrycode, postalcode, placename, adminname1, admincode1, adminname2, admincode2, adminname3, admincode3, latitude, longitude, @accuracy) -- OPTIONALLY ENCLOSED BY '"' -- LINES TERMINATED BY '\n' SET id = NULL, accuracy = nullif(@accuracy,''); |
The server complained, so i had to edit single lines in a >1.000.000 lines text file.
sed was my friend:
check lines from …to:
|
1 |
sed -n '39201,39204p' allCountries.txt |
delete line 39203:
|
1 |
sed -i '39203d' allCountries.txtsed -i '39203d' allCountries.txt |
add line below 39202:
|
1 |
sed -i '39202 a AT\t1010\tWien, Innere Stadt\tWien\t09\tPolitischer Bezirk Wien (Stadt)\t900\tGemeindebezirk Innere Stadt\t901\t48.2077\t16.3705\t3\n' allCountries.txt |
Thanks for the great sed tutorial:
http://www.yourownlinux.com/2015/04/sed-command-in-linux-delete-lines-from-file.html
