load data infile with unknown system variable

User, date Message
Written by pimentones
4 months ago
Category: General
3 posts since Sun, 13 Jan 13 		I´ve been trying to use load data infile procedure to load data from a csv file to mysql, althougt I'ven been unable to due it because of a unknown variable error in the set statement.

LOAD DATA LOCAL INFILE 'D:\\Bets\\BD\\tables\\match.csv'
INTO TABLE `bets`.`match`
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
(`idMatch`, `idChampionship`, @date, `homeTeam`, `awayTeam`, `homeTeamGoals`, `awayTeamGoals`, `matchType` );

SET matchDate = STR_TO_DATE(@date, 'YYYY-MM-DD')



The date field to import is in format 2011-08-07 and MySQL server 5.1.

when running the query the error given is: SQL Error (1193): Unknown system variable 'matchDate'.

any help would be pappreciated... thanks!
Written by ansgar
4 months ago
3966 posts since Fri, 07 Apr 06 		The LOAD command works but the second query fails, or? So, if you want to set a local session variable. Is the @date variable something you have set previously, or what is it?

Setting a local variable:

SET @date:='2011-11-11';
SET @matchDate:=STR_TO_DATE(@date, 'YYYY-MM-DD')
Written by pimentones
4 months ago
3 posts since Sun, 13 Jan 13 		This is the result when I run the query in phpmyadmin:

LOAD DATA LOCAL INFILE 'D:\\Bets\\BD\\tables\\match.csv'
INTO TABLE `bets`.`match`
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
(`idMatch`, `idChampionship`, @date, `homeTeam`, `awayTeam`, `homeTeamGoals`, `awayTeamGoals`, `matchType` );
# 380 rows affected.


SET @matchDate =STR_TO_DATE(@date, 'YYYY-MM-DD')
# MySQL returned an empty result set (i.e. zero rows).



date variable is in the field list and is the field of the csv with the date I want to import. How do I need to use it so I can read the data from the different rows of the file?

thanks for your help :)
Written by jfalch
4 months ago
224 posts since Sat, 17 Oct 09 		if date is meant as a field of the table, omit the @ prefix; these are only used for user variables (see herehttp://dev.mysql.com/doc/refman/5.1/en/set-statement.html).

"How do I need to use it so I can read the data from the different rows of the file?" you cannot read from the file (match.csv); you can only read from the table you just LOADed - eg via SELECT date FROM bets , or SELECT STR_TO_DATE(date, 'YYYY-MM-DD') FROM bets - this will get you a result set which contains the date fields from all rows of the table.

what to do after / instead of that depensds on what you want to do with the data - what do you want to do with the bets.date´s in your SQL ?
Written by pimentones
4 months ago
3 posts since Sun, 13 Jan 13 		so for order I should:

- load first the table match with all the info from the csv;
- then perform a select to convert the data from column date;

To do the last step what do you recommend?!
I was thinking of create an aux column to import the date as varchar and then convert it to the appropriate format.
Written by jfalch
4 months ago
224 posts since Sat, 17 Oct 09 		any recommendations must depend on an answer to the questions:
a) what format is the date field in match.csv ?
b) what do you want to DO with the dates ? do you just want to normalize dates that are not in format YYYY-MM-DD so that they csn be put into a column of type DATE ?
Written by jfalch
4 months ago
224 posts since Sat, 17 Oct 09 		if b) is true, try

UPDATE bets SET date=STR_TO_DATE(date,'YYYY-MM-DD')

or use another column of type DATE and replace date= with its name (plus =).
 

Please login to leave a reply, or register at first.