import csv file with data to mysql

Question

I'm trying to import csv file to MYSQL, and I have the following schema.

CREATE TABLE `monitor` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`time` time DEFAULT NULL,
`domain_name` text,
`cpu_ns` int(11) DEFAULT NULL,
`cpu_percentage` int(11) DEFAULT NULL,
`mem_bytes` int(11) DEFAULT NULL,
`mem_percentage` int(11) DEFAULT NULL,
`block_rdby` int(11) DEFAULT NULL,
`block_wrby` int(11) DEFAULT NULL,
`net_rxby` int(11) DEFAULT NULL,
`net_wrby` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

I'm having issues importing a file with data presented as follows.

16:48:27,2,s-1-VM,220000000.,0.448204684384,262144,0,0,0,60,0
16:48:30,2,s-1-VM,260000000.,0.528932926209,262144,0,0,16384,300,0
16:48:33,2,s-1-VM,300000000.,0.609786677944,262144,0,0,0,180,0
16:48:37,2,s-1-VM,290000000.,0.59000206364,262144,0,0,16384,120,0
16:48:40,2,s-1-VM,270000000.,0.54985661784,262144,0,0,0,649,425
16:48:43,2,s-1-VM,310000000.,0.631207212346,262144,0,0,0,180,0
16:48:46,2,s-1-VM,220000000.,0.44728232907,262144,0,0,20480,60,0
16:48:49,2,s-1-VM,200000000.,0.407008216196,262144,0,0,0,300,0
16:48:52,2,s-1-VM,250000000.,0.508946559213,262144,0,0,0,240,0
16:48:55,2,s-1-VM,240000000.,0.488674160215,262144,0,0,0,120,0

How can import this to my database?

I have tried the following and I get lots of warnings.

LOAD DATA LOCAL INFILE '/tmp/domain2.csv' INTO TABLE vtop  FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

your help is highly appreciated.

Thank you

flaschenpost · Accepted Answer · 2013-05-08 21:46:06Z

3

If I understand http://dev.mysql.com/doc/refman/5.1/de/load-data.html correctly, you should set ID to null (that makes it auto_increment) via

"SET id=NULL "

at the end of the statement. Otherwise column counts and column orders have to match perfectly.

But your columns don't match at all (what is the "2" at position 2?). So create a temp-Table with the structure of your CSV and then assign via insert into ... select the matching columns.

answered May 8, 2013 at 21:46

flaschenpost

2,2352 gold badges15 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

flaschenpost Over a year ago

I have not yet known the possibility to list the column names as given in the other answers, just be careful with the strange second column and if it is not needed in the table then put it in a variable like <at>dummy1, as in (time, @dummy1, domain_name, cpu_ns...)

Deano Over a year ago

You were right about the ID, and about the mistake in the columns and schema! a big thank you to you!

Marc B · Accepted Answer · 2013-05-08 21:48:04Z

0

MySQL isn't an AI and can't figure out that 16:48:27 should go into the the time field - it'll be trying to stuff that into id instead.

You need to explictly map the columns in your CSV file to the fields they should go into in the table:

LOAD DATA .... (time, domain_name, x, y, z, foo, bar)

answered May 8, 2013 at 21:48

Marc B

362k44 gold badges433 silver badges508 bronze badges

Collectives™ on Stack Overflow

import csv file with data to mysql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related