0

I have several thousand csv files that I'm attempting to load into a SQL database. I had written a stored procedure to iterate through the file names loaded into a separate table before quickly realizing that the 'LOAD DATA INFILE' command does not work with variable path names.

I then attempted to use a prepared statement to get around this, but the command does not work in a prepared statement either.

Thus, I came to my current workaround of writing the command using variable paths into a separate file, then running the file. However, when running the procedure, I end up with a syntax error around the 'LOAD DATA INFILE' command, but I can't figure out where the issue is coming from.

My code for the procedure is as follows:

DELIMITER //

CREATE PROCEDURE TEST()
BEGIN

DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE tn TEXT DEFAULT '';
DECLARE p TEXT DEFAULT '';
DECLARE sp TEXT DEFAULT '';
SELECT COUNT(*) FROM `2020_list` INTO n;
SET i = 0;

WHILE i < n DO
    SELECT `table_column3` FROM table_name WHERE `table_column1` = i INTO tn;
    
    SET @ct := concat("CREATE TABLE ", tn, " 
    (.......table column information......);");
    PREPARE stmt1 FROM @ct;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
    
    SELECT `table_column2` FROM table_name WHERE `table_column1` = i INTO p;
    
    SET @ft := concat("LOAD DATA LOCAL INFILE '", p, "' 
    INTO TABLE `", tn, "` 
    FIELDS TERMINATED BY ',' 
    LINES TERMINATED BY '\n' 
    IGNORE 1 ROWS;");
    
    SELECT `table_column4` FROM 2020_list WHERE `table_column1` = i INTO sp;
    
    SET @cf := concat("SELECT ", @ft, " INTO OUTFILE ", sp, ";");
    PREPARE stmt2 FROM @cf;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2;
    
    SET @rf := concat("SOURCE ", sp, ";");
    PREPARE stmt3 FROM @rf;
    EXECUTE stmt3;
    DEALLOCATE PREPARE stmt3; 
    
    SET i = i + 1;
END WHILE;
END;
//

When running the procedure, I end up with the following 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 'LOAD DATA LOCAL INFILE 'Path Name' at line 1

I have pulled various portions of the procedure out and ran them as commands and receive the same result. However, everything that comes out of each variable is what I am expecting and runs as expected in the editor.

Is this a hopeless cause as a workaround to the no variable path names for 'LOAD DATA INFILE', or am I just missing something blaringly obvious in my code? I have another potential workaround, but I was really hoping to automate this process.

starks5201
  • 1
  • Side note: "`table_name`", "`table_column1`", "`table_column2`", "`table_column3`", ... -- The names of your identifiers aren't exactly great at helping to understand what is what... Consider using more descriptive ones. – sticky bit Jan 15 '22 at 21:53
  • 2
    Side note: Don't get used to use double quotes for string or date literals. Yes, sadly MySQL accepts that, but in SQL double quotes are usually for identifiers, such as column names. Should you ever use another DBMS (or future MySQL versions become more sane about this) you'll likely get an "invalid object name" error. Always use single quotes for string or date literals. – sticky bit Jan 15 '22 at 21:55
  • you can't add the laod data int a SELECT `SELECT ", @ft, " `makes no sense at all – nbk Jan 15 '22 at 22:00
  • Debug your procedure. Create debug table and insert into each SQL text composed in the variable before PREPARE execution. Then investigate your procedure flow and SQL text correctness. – Akina Jan 15 '22 at 22:03
  • @stickybit, I tried genericizing all of the columns so as to not get bogged down in the details of the individual tables. I can post the exact code if necessary. Also, thanks for the tip on double quotes, I'll be sure to address that moving forward. – starks5201 Jan 16 '22 at 00:36
  • @nbk, I got the idea for that from this thread: https://stackoverflow.com/questions/25380903/mysql-load-data-infile-with-variable-path I didn't actually test to see if it was possible, but I guess if it isn't I'll write off the exercise and go with my workaround. – starks5201 Jan 16 '22 at 00:39
  • @starks5201 the thread says nothing about that , the code is illegal, run first the load data and then the select – nbk Jan 16 '22 at 00:45
  • @nbk, sorry, I dropped the wrong link. https://stackoverflow.com/questions/14523816/load-data-infile-variable-into-infile-error – starks5201 Jan 16 '22 at 18:55
  • Additionally, I was able to get that part of the script to work in creating the SQL script file. Now the issue is in getting the procedure to call and run that file. The issue with that part was that the 'LOAD DATA INFILE' code within the SELECT...INTO OUTFILE needed to be in quotes. – starks5201 Jan 16 '22 at 19:02
  • @starks5201 you didn't understamd the code in the link provided, the select only writes the Load command into a sql text file and last he executes exactly that new file, so as i said you can use laod data in that way – nbk Jan 16 '22 at 19:21
  • Small advice: Break your code into small procedures then call them with a main procedure. In this way, you can test each piece separatetly, then test together. It gets easier to find out bugs this way, plus, you might end with reusable procedures for other projects. – acarlstein Jan 25 '22 at 18:02

0 Answers0