Loading of a csv file into MySQL is not working!

Databases
1

Hello,

I am trying to load the content of a csv file into my MySQL Table, but after the loading is done
all the fields of the Table are NULL! I mean it creates (loads) 111799 rows of the csv file in
the resulting Table, but the fields of the Table are all empty!

Here is the Table:

CREATE TABLE ip_lookup (
id int(11) NOT NULL auto_increment,
ip_from bigint(12) default NULL,
ip_to bigint(12) default NULL,
country_code2 char(2) default NULL,
country_code3 char(3) default NULL,
country_name varchar(50) default NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

And the command I am using to load the csv file into the MySQL Table is:

LOAD DATA INFILE ‘ip-to-country.csv’ INTO TABLE dd.ip_lookup;

What is going wrong?

Regards,

2

Is your CSV file really comma separated? I know that it “should” be but I’ve caught myself trying to upload a tab delimited file this way and had problems because MySQL can’t read the data. You may need to specify your field terminated by and escaped by values.

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

3

Can you post a few lines of data from your csv file?

It might also have something to do with the way each line is terminated in the csv file or the some of the data might need “cleaning up” or maybe some of the data is not the correct type according to your db table’s column definitions or maybe some data for some fields in your csv might be missing.

4

Hi,

Here is an excerpt from the csv file, that is supposed to be comma delimited:

16777216 17367039 AU AUS AUSTRALIA
17367040 17432575 MY MYS MALAYSIA
17432576 17498111 AU AUS AUSTRALIA
17498112 17563647 KR KOR REPUBLIC OF KOREA
17563648 17825791 CN CHN CHINA
17825792 18087935 KR KOR REPUBLIC OF KOREA
18087936 18153471 AU AUS AUSTRALIA
18153472 18219007 JP JPN JAPAN
18219008 18350079 IN IND INDIA
18350080 18874367 CN CHN CHINA
18874368 18939903 AU AUS AUSTRALIA
18939904 19005439 JP JPN JAPAN
19005440 19136511 TW TWN TAIWAN

5

Doesn’t look comma delimited to me. :eek:

LOAD DATA INFILE ‘ip-to-country.csv’ INTO TABLE dd.ip_lookup [{FIELDS | COLUMNS}[TERMINATED BY ’ ']];

I think that should work, but it’s untested and I don’t do this very often. At least it should give you something to go on.

6

Thanks.
I got it finally working with this:

load data infile ‘ip-to-country.csv’ into table ip_lookup fields terminated by ‘,’ enclosed by ‘"’ lines terminated by '\r
';

7

I find tab delimited more reliable, as data strings can contain a comma or a space (at least in my case), but never a tab.
republic of korea for example contains spaces which is causing you to write such a long load data command.

Personally I just pass the data extract I am sent through excel, save as tab delimited txt and it works every time, with no need to state delimiter or end of line. Using HeidiSQL, or SQL Manager Lite I just edit the file name each time.

8

Yes, I agree that tab delimited is more reliable. That is why at the end I went with that.

But let me ask you about something related, well related to why I was loading a file into MySQL. That is the file that I uploaded is of IP to Country mapping. However what I need is a free list of IP to Country, State, City, etc. information. Do you happen to know of such a free list?

ThanX,