Having issues in assigning mysql datatypes

Posted on

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)
    ...;

Leave a Reply

Your email address will not be published.