Having issues in assigning mysql datatypes

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.

• Sunil Nepali: I guess GMT is making this issue. Try it again removing GMT from date time value. This may help you
• John K. N.: How are you importing the CSV file (Comma Separated Values) into your table? I'm asking because your data is not comma separated. Which tool/program are you using? Supply as much information as possible by editing your question and adding the specifics.
• Vérace: Is this tab separated data? That's fine - take a look here. What is it, exactly, that you are measuring with a precision of 10e-7 Celsius?
• LowlyDBA - John M: It seems like you may need to merge your two accounts, @leopard.
• RDFozz: As a general tip: look at rows that are not getting loaded. Look throughout the file, in case the data you're being handed changes at some point. Also, consider loading to a staging table, setting all values to nvarchar (varchar is OK if you're sure that you aren't getting Unicode data), and perform appropriate conversions to load the data to its ultimate destination.

Answer 1

To get rid of " GMT" in the fly, use @variables and SET. Something like

LOAD ... 
 ... ,
 ( cell, id, too, @issue, ... )
 SET issue = LEFT(@issue, 19)
 ...;