0

I am attempting to fill a MySQL table from CSV files where each CSV represents data from one day of the year. They are named from 1.csv to 364.csv (for some reason, NYE wasn't exported).

I have set up a loop to complete the data import to iterate through the files.

SET @d = 1;
WHILE @d < 365 DO
  LOAD DATA LOCAL INFILE 
    CONCAT('F:\\AVM\\2019\\', @d, '.csv') 
    INTO TABLE `avm`.`history` 
    FIELDS ESCAPED BY '\\' TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
    IGNORE 0 LINES (@route, @avm, @service, @arr, @dep)
    SET `route_id` = (SELECT id FROM `routes` WHERE `route_name` = @route),
        `location_id` = (SELECT id FROM `locations` WHERE `code` = @avm),
        `service_id` = (SELECT id FROM `services` WHERE `service` = @service),
        `date` = (SELECT DATE_ADD('2018-12-31', INTERVAL @d DAY)),
        `arr` = @arr,
        `dep` = @dep,
        `direction` = @dir;
  SET @d = @d + 1;
END WHILE;

I get the error:

Error Code: 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 'WHILE @d < 365 DO
  load data local infile 
    CONCAT('F:\\AVM\\2019\\', @d, ' at line 1

I have also tried (SELECT CONCAT('F:\\AVM\\2019\\', @d, '.csv')); as well.

Is this the correct approach? Or should I be doing this a completely different way?

SimpleProgrammer
  • 323
  • 3
  • 25
  • Statements have to be inside a stored procedure, they're not valid queries by themselves. – Barmar Mar 24 '20 at 06:48
  • Filenames in `LOAD DATA INFILE` have to be literal strings, not expressions. See the duplicate for how to work around this. – Nick Mar 24 '20 at 06:49

0 Answers0