Loading multiple csv files to MariaDB/MySQL through bash script

Question

After trying for a full day, I'm hoping someone here can help me make below script work. I've combined information from multiple threads (example) and websites, but can't get it to work.

What I'm trying to do:

I'm trying to get a MariaDB10 database called 'stock_db' on my Synology NAS to load all *.csv files from a specific folder (where I save downloaded historical prices of stocks) and add these to a table called 'prices'. The files are all equally named "price_history_'isin'.csv". Below SQL statement works when running it individually from HeidiSQL on my Windows machine:

Working SQL

LOAD DATA LOW_PRIORITY LOCAL INFILE 'D:\\Downloads\\price_history_NL0010366407.csv'
IGNORE INTO TABLE `stock_db`.`prices`
CHARACTER SET utf8
FIELDS TERMINATED BY ';'
            OPTIONALLY ENCLOSED BY '"'
            ESCAPED BY '"'
            LINES TERMINATED BY '\r\n'
            IGNORE 2 LINES
(@vdate, @vprice)
SET
    isin = 'NL0010366407',
    date = STR_TO_DATE(@vdate, '%d-%m-%Y'),
    price = @vprice
;

The issue

Unfortunately, when I try to batch loading all csv's from a folder on my NAS through below script, I keep getting the same error.

#!/bin/bash

for filename in ./price_history/*.csv; do
echo $filename
isin=${filename:30:12}
echo $isin
/volume1/@appstore/MariaDB10/usr/local/mariadb10/bin/mysql -u root -p \
    "LOAD DATA LOW_PRIORITY LOCAL INFILE '$filename'\
    IGNORE INTO TABLE 'stock_db.prices'\
        CHARACTER SET utf8\
        FIELDS TERMINATED BY ';'\
        OPTIONALLY ENCLOSED BY '"'"'"'\
        ESCAPED BY '"'"'"'\
        LINES TERMINATED BY '\r\n'\
        IGNORE 2 LINES (@vdate, @vprice)\
    SET\
        isin = '$isin',\
        date = STR_TO_DATE(@vdate, '%d-%m-%Y'),\
        price = @vprice;"
done

ERROR 1102 (42000): Incorrect database name

What I've tried

Took the database name out of stock_db.prices and mentioned it separately as [database] outside of the quoted SQL statement - Doesn't work
Changed quotes around 'stock_db.prices' in many different ways - Doesn't work
Separated the SQL into a separate file and referenced it '< stmt.sql' - Complicates things even further and couldn't get it to work at all (although probably preferred)
Considered (or even preferred) using a PREPARE statement, but seems I can't use this in combination with LOAD DATA (reference)

Bonus Question

If someone can help me do this without having to re-enter the user's password or putting the password in the script, this would be really nice bonus!

Update

Got the 'Incorrect Database Error' resolved by adding '-e' option Now I have a new error on the csv files:

ERROR 13 "Permission Denied"

While the folder and files are full access for everyone. Anyone any thoughts to this? Thanks a lot!

olegsv · Answer 1 · 2019-01-05T09:57:47.837

0

Try to set database using -D option: change the first line to /volume1/@appstore/MariaDB10/usr/local/mariadb10/bin/mysql -D stock_db -u root -p \ ...
You may have an error in this line IGNORE INTO TABLE 'stock_db.prices'\ - try to remove the single quotes.
Create file .my.cnf in your user's home directory and put the following information into it: [client] password="my password" Info about option files.

edited Jan 05 '19 at 09:57

answered Jan 05 '19 at 09:52

olegsv

1,422
1
14
21

Thanks for the effort, but I'm still getting the same error. I'll investigate those option files a bit more... Thanks! – Joost Jan 05 '19 at 10:12

score 0 · Answer 2 · answered Jan 06 '19 at 04:57

'stock_db.prices'

Incorrect quoting. This will work since neither are keywords:

stock_db.prices

This will also work:

`stock_db`.`prices`

Note that the db name and the table name are quoted separately, using backtics.

I can't predict what will happen with this nightmare:

'"'"'"'