Re: bulk import script for .csv files

[Alan LaPenn]

Justin is there only one field in the CSV file that you are loading?
Do all of the files have the same format and does the table CSVList have that same format?

They key here is the end of the LOAD FILE line "SET`CSVFilename`='A.csv';"
this part of the statement is saying load the field named CSVFilename in table CSVList with the string A.csv', is that what you really want to do?

Alan

Justin Hibbard Wrote:
-------------------------------------------------------
> 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 < 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