Question :
I am working first time with database. I had never inserted data into database. I want to insert below csv into mysql database. But I facing issues with data types. What type of data type I should assign to the variables.
"cell","id","too","Issue","Valid","DPT","RH","TMP","U","V"
"1",1,22383,"2015-01-15 00:00:00 GMT","2015-01-15 12:00:00 GMT",267.800018310547,91.5999984741211,269.040008544922,-0.529999971389771,-1.78999996185303
"2",2,24532,"2015-01-15 00:00:00 GMT","2015-01-15 12:00:00 GMT",272.600006103516,88.5,274.259979248047,-1.58999991416931,-0.239999994635582
"3",3,23936,"2015-01-15 00:00:00 GMT","2015-01-15 12:00:00 GMT",271,88.2000045776367,272.72998046875,-0.429999977350235,-1.01999998092651
Could anyone help me in figuring out the data types of the columns?
I have assigned datatypes below but I am getting so many values while inserting data:
CREATE TABLE `raster_cell_data` (
`cell` varchar(100) NOT NULL,
`id` varchar(100) NOT NULL,
`too` int(14) NOT NULL,
`Issue` datetime DEFAULT NULL,
`Valid` datetime DEFAULT NULL,
`DPT` varchar(100) DEFAULT NULL,
`RH` varchar(100) DEFAULT NULL,
`TMP` varchar(100) DEFAULT NULL,
`U` varchar(100) DEFAULT NULL,
`V` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
I am loading data using the below command:
LOAD DATA LOCAL INFILE 'C:\Users\vkaushik\Desktop\Temp.csv'
INTO TABLE raster_cell_data
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'n';
Getting Error 1234: out of the range
for many variables while loading. And only 101 rows are getting inserted out of 901.
Thanks.
Answer :
To get rid of ” GMT” in the fly, use @variables and SET. Something like
LOAD ...
... ,
( cell, id, too, @issue, ... )
SET issue = LEFT(@issue, 19)
...;