import CSV files into MySQL with BASH

[user1960]

Hello,
I try to write script for importing CSV data into MySQL database. MySQL was installed as component of Zabbix, according to this manual: https://bestmonitoringtools.com/how-to-install-zabbix-server-on-ubuntu/
I keep getting following error:

./import_v2.sh: 45: Bad substitution

It looks like some comon bash problem, but not sure how to correct it. Can anyone help?

Thank you!


Here is the code I use:

#!/bin/bash

# show commands being executed, per debug
set -x

# define database connectivity
_db="mysql"
_db_user="root"
_db_password="rootDBpass"

# define directory containing CSV files
_csv_directory=/home/ubuntu

# go into directory
cd $_csv_directory
echo $_csv_directory
# get a list of CSV files in directory
_csv_files=`ls -1 *.csv`
echo $_csv_files
# loop through csv files
for _csv_file in $_csv_directory/*.csv ;
do

  # remove file extension
  _csv_file_extensionless=`echo $_csv_file | sed 's/\(.*\)\..*/\1/'`

  # define table name
  _table_name="${_csv_file_extensionless}"

  # get header columns from CSV file
  _header_columns=`head -1 $_csv_file | tr ',' '\n' | sed 's/^"//' | sed 's/"$//' | sed 's/ /_/g'`
  _header_columns_string=`head -1 $_csv_file | sed 's/ /_/g' | sed 's/"//g'`

  # ensure table exists
  mysql -u $_db_user -p$_db_password $_db << eof
    CREATE TABLE IF NOT EXISTS \`$_table_name\` (
      id int(11) NOT NULL auto_increment,
      PRIMARY KEY  (id)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1
eof

  # loop through header columns
  for _header in ${_header_columns[@]}
  do

    # add column
    mysql -u $_db_user -p$_db_password $_db --execute="alter table \`$_table_name\` add column \`$_header\` text"

  done

  # import csv into mysql
  mysqlimport --fields-enclosed-by='"' --fields-terminated-by=',' --lines-terminated-by="\n" --columns=$_header_columns_string -u $_db_user -p$_db_password $_db $_csv_directory/$_csv_file

done
exit

 

[dbdemon]

Which line do you get the error on?

Googling the error message ("bad substitution") suggests you might not be running this as a bash script, but rather with dash which is installed at / symlinked from /bin/sh on some Linux distros. Although there could be other explanations.