distal-attribute
distal-attribute
distal-attribute
distal-attribute

load data infile with unknown system variable

pimentones posted 2 years ago in General
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!
ansgar posted 2 years ago
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')

pimentones posted 2 years ago
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 :)
jfalch posted 2 years ago
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 ?
pimentones posted 2 years ago
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.
jfalch posted 2 years ago
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 ?

jfalch posted 2 years ago
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.