how to create a bulk mysql upload script in linux

Question

I have a mysql datbase with a tables that have the same column names as csv files that i recieve. I used to use a windows batch file to upload then into mysql.

@echo off
echo TRUNCATE TABLE `alarms`; > importalarm.sql
echo Creating list of MySQL import commands
for %%s in (*.csv) do 
  echo LOAD DATA LOCAL INFILE '%%s' INTO TABLE `alarms` 
       FIELDS TERMINATED BY ',' ENCLOSED BY ' ' 
       ESCAPED BY '/'LINES TERMINATED BY '\r\n' IGNORE 1 LINES; >> importcsv.sql;
echo mysql -u root -p uninetscan < importalarm.sql

I need to turn this into a linux script.

Any ideas.

Answer 1

Here's how you'd do something similar on linux:

#!/bin/bash
echo 'TRUNCATE TABLE `alarms`;' > importalarm.sql
echo "Creating list of MySQL import commands"
for f in *.csv; do
    echo "LOAD DATA LOCAL INFILE '$f' INTO TABLE \`alarms\` FILEDS TERMINATED BY ',' ENCLOSED BY ' ' ESCAPED BY '/' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;" >> importcsv.sql;
done
echo 'mysql -u root -p uninetscan < importalarms.sql'

I noticed that you are sending the output to 2 files (importalarm.sql and importcsv.sql), not sure if it's what you want, but it's really easy to change. Also, the last line is just echoing the command, not actually executing it. If you want to execute it, just remove the echo and quotes.