bulk import script for .csv files

[Justin Hibbard]

Anyone who can solve this problem will get a handy little script for bulk importing .csv files into MySQL. I'm almost there except for one detail.

I found this Windows batch script that, when run in the same directory as a bunch of .csv files, will generate an SQL script that lists LOAD DATA INFILE commands for all of the .csv files in the directory:

@echo off
echo TRUNCATE TABLE `CSVList`; > importcsv.sql
echo Creating list of MySQL import commands
for %%s in (*.csv) do echo LOAD DATA LOCAL INFILE '%%s' INTO TABLE `CSVList` FIELDS TERMINATED BY ',' ENCLOSED BY ' ' ESCAPED BY '/'LINES TERMINATED BY '\r\n' SET `CSVFilename`='%%s'; >> importcsv.sql;
echo Now run "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysql" -u root -p [password] [dbname] < importcsv.sql

It works great as far as generating the list of LOAD DATA commands, which look like this:

LOAD DATA LOCAL INFILE 'A.csv' INTO TABLE `CSVList` FIELDS TERMINATED BY ',' ENCLOSED BY ' ' ESCAPED BY '/'LINES TERMINATED BY '\r\n' SET `CSVFilename`='A.csv'; ;

But when MySQL tries to execute the commands, it returns error 1054 "Unknown column 'CSVFilename' in 'field list'." It doesn't like something about the SET `CSVFilename`='%%s' command. Any ideas?

Justin