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.

Can you, please, format your script?

Mano Kovacs
– Mano Kovacs

2011-01-24 16:25:59 +00:00
Commented Jan 24, 2011 at 16:25 — Mano Kovacs
– Mano Kovacs, Commented Jan 24, 2011 at 16:25

Vlad H · Accepted Answer · 2011-01-24 16:37:09Z

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.

answered Jan 24, 2011 at 16:37

Vlad H

3,6891 gold badge21 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to create a bulk mysql upload script in linux

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related