Loading CSV FILE INTO MYSQL DB System.

At first, I tried to use the command below.

“mysqlimport –ignore-lines=1 –fields-terminated-by=, –columns=’beginIp,endIp,beginIpNum,endIpNum,countryCode, countryName’ –local -u argusUser -p argusData /home/gstech/argus/GeoIP_data/geoIP.csv”

To use this command, we need to create a table with same name for that csv file as “geoIP”.

Then the data in csv file will be inserted to that table.

here “—ignore-lines=1” means ignoring the first line for inserting the table.

“–fields-terminated-by=,” describes the delimiter for the csv file.

–columns=’’ specifies  the columns in the tables and the field names which will be inserted to the db table.

 

BUT, It was pretty complicated and needed so much effort to use it since the csv files contains the “,” which is delimiter for my csv file, and I didn’t want to put any effort to change the csv file.

 

After Google a while for that question, “LOAD IN FILE” in mysql solved my problem.

LIKE This: “LOAD DATA INFILE ‘/home/gstech/argus/GeoIP_data/GeoIPOrg-141.csv’ INTO TABLE geoIPOrgIP COLUMNS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘ ESCAPED BY ‘”‘ LINES TERMINATED BY ‘\n’ IGNORE 2 LINES (beginIp,endIp,countryCode,orgName); ”

 

 

And before using this command, there were also several tiny problems. Here let me describe and explain how to solve them.

 

First Problem:
mysql> LOAD DATA INFILE “/home/gstech/argus/GeoIP_data/test/geoIPOrg.csv” 
    -> INTO TABLE geo;
ERROR 1045 (28000): Access denied for user ‘argusUser’@’localhost’ (using password: YES)

 

Solution:

Here we need to grant “FILE” privillege to the user. So change to the root first, mysql -u root -p
then type “grant file on *.* to argusUser@localhost;”

 

Second Problem:

mysql> LOAD DATA INFILE “/home/gstech/argus/GeoIP_data/test/geoIPOrg.csv” INTO TABLE geoIPOrg COLUMNS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘ ESCAPED BY ‘”‘ LINES TERMINATED BY ‘\n’ (beginIpNum, endIpNum,orgName);
ERROR 13 (HY000): Can’t get stat of ‘/home/gstech/argus/GeoIP_data/test/geoIPOrg.csv’ (Errcode: 13)

 

Solution:

This problem is solved by adding permission to the all parent directoreis.
Since All parent directories of the infile need world-readable I think aswell as just the directory and infile, mine did on CentOS 4.3 with MySQL 4.1.12-log.
So for an infile here:
“/home/gstech/argus/GeoIP_data/test/geoIPOrg.csv”

we need to use chmod and add permissions for them.

[gstech@localhost test]$ chmod 755 /home/gstech/argus/GeoIP_data/test/  
[gstech@localhost test]$ chmod 755 /home/gstech/argus/GeoIP_data/
[gstech@localhost test]$ chmod 755 /home/gstech/argus/
[gstech@localhost test]$ chmod 755 /home/gstech/

 

Then, our magic word:

“LOAD DATA INFILE ‘/home/gstech/argus/GeoIP_data/GeoIPOrg-141.csv’ INTO TABLE geoIPOrgIP COLUMNS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘ ESCAPED BY ‘”‘ LINES TERMINATED BY ‘\n’ IGNORE 2 LINES (beginIp,endIp,countryCode,orgName);”

Worked like a charm 🙂

 

^_____________________________________________________________^

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s