How does one load IP addresses into Mysql Table from a CSV file?

Question

I'm trying to find a way to load a mysql table which I call ipCompare_tbl with IP addresses from a CSV file called myIPs.csv.

The fields in ipCompare_tbl are ipStart, and ipEnd. I've also included an auto_incrementor for a primary key generator field called id.

My goal is to have ipCompare_tbl loaded with:

      ipStart      ipEnd      id
    123.10.0.0   123.0.0.255   1
    130.20.0.0   130.0.0.255   2
    140.30.0.0   140.0.0.255   3

I keep getting the following error: ERROR 1366 (HY000): Incorrect integer value: '"130.20.0.0"' for column 'ipStart' at row 1

I'm running the following code for it:

    DROP TABLE IF EXISTS ipCompare_tbl;
    CREATE TABLE ipCompare_tbl(
    ipStart int(10) unsigned NOT NULL,
    ipEnd int(10) unsigned NOT NULL,
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY ( id ));

    LOAD DATA INFILE 'myIPs.csv' INTO TABLE ipCompare_tbl
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY ';'
    IGNORE 1 LINES;

    SELECT INET_ATON(ipStart), INET_ATON(ipEnd)
    FROM ipCompare_tbl;

or varchar(45) if you want to support IPv6: http://stackoverflow.com/questions/1076714/max-length-for-client-ip-address — WOUNDEDStevenJones, Mar 24 '17 at 23:22

score 0 · Answer 1 · answered Mar 24 '17 at 23:22

0

Change your table schema to

CREATE TABLE ipCompare_tbl(
ipStart varchar(15) NOT NULL,
ipEnd varchar(15) NOT NULL,
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY ( id ));

answered Mar 24 '17 at 23:22

David Brossard

13,584
6
55
88

Tried the varchar and it returns: ERROR 1261 (01000): Row 1 doesn't contain data for all columns – Mike Addison Mar 24 '17 at 23:28
It's not due to varchar. Your loading script says your fields should be comma-separated but you use tabs in your csv – David Brossard Mar 24 '17 at 23:32

Barmar · Answer 2 · 2017-03-24T23:56:15.617

0

You need to convert the IP addresses to numbers when you're loading the CSV.

LOAD DATA INFILE 'myIPs.csv' INTO TABLE ipCompare_tbl
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY ';'
IGNORE 1 LINES
(@ipstart, @ipend, id)
SET ipStart = INET_ATON(@ipstart), ipEnd = INET_ATON(@ipend);

Then you don't need to use INET_ATON when you're selecting, since it's already numbers.

edited Mar 24 '17 at 23:56

answered Mar 24 '17 at 23:23

Barmar

741,623
53
500
612

It's giving me grief about the load statement: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY ';' IGNORE 1 LINES ' at line 3 – Mike Addison Mar 24 '17 at 23:49
I'm using the varchar(15) David suggested along with the following load from Barmar: LOAD DATA INFILE '/Volumes/G- DRIVEmobileUSB/MySQL/myIPs.csv' INTO TABLE ipCompare_tbl (@ipstart, @ipend, id) FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY ';' IGNORE 1 LINES SET ipStart = INET_ATON(@ipstart), ipEnd = INET_ATON(@ipend); – Mike Addison Mar 24 '17 at 23:51

How does one load IP addresses into Mysql Table from a CSV file?

2 Answers2