|
|
Posted by Thomas Bartkus on 11/17/05 04:40
On Wed, 16 Nov 2005 21:20:01 +0000, BigDave wrote:
> Thomas Bartkus wrote:
>> "BigDave" <spam@spam.com> wrote in message
>> news:dsmdnUVt9KBUsebeRVnyvA@giganews.com...
>>> Hello,
>>>
>>> Can someone suggest a way to import many CSV files into a MySQL table?
>>> All CSV files are stored in one directory and need to be imported one by
>>> one.
>>> This is on a Windows server.
>>
>> Look up "LOAD DATA INFILE".
>>
>> OR perhaps the problem that you know how to import CSV files but just don't
>> know how to script it?
>>
>> A sequence of "LOAD DATA INFILE" commands, properly terminated by ";", in a
>> plain text file that you pass to the MySQL utility thus:
>>
>> mysql -u {username} -p {password} sql_script.txt
>>
>> Thomas Bartkus
>>
>>
> Did you mean this (with the file redirector added)? If so that works for me
>
> mysql -u {username} -p {password} < sql_script.txt
>
>
> I did a little reading on batch files in Windows and came up with this
> very simple one:
>
> @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:\wamp\mysql\bin\mysql -u root dbname < importcsv.sql
>
> Which, when run from a directory containing .csv files creates a file
> called importcsv.sql which I can then run from MySQL.
Sorry about missing that file redirector, but you've obviously got it.
My DOS scripting has long gone stale. But you could do the same with VB,
VBA behind any MS Office app, VBScript, or my favorite - Python. You can
use most anything so long as you are throwing legal SQL statements at the
command line mysql utility.
Thomas Bartkus
Navigation:
[Reply to this message]
|