CSV Load Data into mySQL with mismatched and skipped columns

Question

I am attempting to load data from a csv into mySQL. The columns are not in the right order and some are skipped. Dates are also in the wrong format

Data looks like this:

Date,Open,High,Low,Close,Adj Close,Volume 4/3/2006,10.01,10.01,10.01,10.01,7.236886,0 4/4/2006,10.07,10.07,10.07,10.07,7.280261,0

My code looks like this:

$query = <<<eof
    LOAD DATA INFILE '$filename'
      INTO TABLE `$table`
      FIELDS TERMINATED BY ','
      LINES TERMINATED BY '\r\n'    
      IGNORE 1 LINES
      (@date_str, @open, @high, @low, @close, @adjclose, @volume)
      SET `date` = STR_TO_DATE(@date_str, '%c/%e/%Y'),
      SET symbol = $symbol,
      SET nav = @close; 
eof;

The error I'm getting is this:

Sql Error: LOAD DATA INFILE 'D:/Website/finance/csv/ABNDX.csv' INTO TABLE mutual_fund_history FIELDS TERMINATED BY ',' LINES TERMINATED BY ' ' IGNORE 1 LINES (@date_str, @open, @high, @low, @close, @adjclose, @volume) SET date = STR_TO_DATE(@date_str, '%c/%e/%Y'), SET symbol = ABNDX, SET nav = @close; Sql error #: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET symbol = ABNDX, SET nav = @close' at line 8…

I'm stummped. Thanks for your help.

Update: I've tried putting single quotes around '$symbol' since it is a string but still get the error.

Note, I've followed this post to guide me.

Answer 1

Ok, got it from that post. Only one SET statement can be used and the different variables must be separated by commas so it should be:

$query = <<<eof
    LOAD DATA INFILE '$filename'
     INTO TABLE `$table`
     FIELDS TERMINATED BY ','
     LINES TERMINATED BY '\r\n'    
     IGNORE 1 LINES
     (@date_str, @open, @high, @low, @close, @adjclose, @volume)
     SET `date` = STR_TO_DATE(@date_str, '%c/%e/%Y'),
      symbol = '$symbol',
      nav = @close;
eof;