Dump a csv file into mysql, creating a table if necessary

csv2mysql.sh

#!/usr/bin/env bash

SCRIPT_PATH=${BASH_SOURCE[0]}
SCRIPT_NAME=${SCRIPT_PATH##*/}
# SCRIPT_DIR="$(cd "$(dirname "${SCRIPT_PATH:-$PWD}")" 2>/dev/null 1>&2 && pwd)"
HELPDOC=$(cat <<HEREDOC
   Usage: $SCRIPT_NAME <file.csv> [--delim <csv_delimter> --db <mysql_database_name> --table <table_name> --user <username> --pass <password> --execute --help]

   Dumps a CSV file into a MySQL table. MySQL table will be created if it
   doesn\'t already exist.

   The script will generate two SQL commands: one to create the table, another
   to dump the csv file into the table. Omit the --execute option to see what
   the commands are without running the commands.

   Examples:
      $SCRIPT_NAME entries.csv
      $SCRIPT_NAME entries.csv --delim ':' --db mydatabase --table customers --user username --pass password123
      $SCRIPT_NAME entries.csv --delim ':' --db mydatabase --table customers --user username --pass password123 --execute

   Options:
      --help       Show this help
      --db         Database name
      --table      Database table to dump data into (will be created if not exists)
      --user       Database username
      --pass       Database password
      --execute    Executes the generated SQL commands at the end of the script
      --delim      Specify delimiter in CSV file (default comma ',')
HEREDOC
)

# Unpack script arguments
argc=$#;: $((i=0))
while [ "$i" -lt "$argc" ]; do
  case "$1" in
    --help|-h) Help='true';;
    --execute) Execute='true';;
    --user) shift;: $((i=$i+1)); User="$1";;
    --pass) shift;: $((i=$i+1)); Pass="$1";;
    --db) shift;: $((i=$i+1)); Db="$1";;
    --table) shift;: $((i=$i+1)); Table="$1";;
    --delim) shift;: $((i=$i+1)); Delim="$1";;
    *) CsvFile="$1";;
  esac
  shift;: $((i=$i+1))
done

# If --help option specified or no arguments passed, print $HELPDOC and exit
if [ "$Help" ] || [ ! "$CsvFile" ] ; then
  echo "$HELPDOC"
  exit 0
fi

# Ensure GNU awk is installed
AWK="$(command -v awk)"
if "$AWK" --version 2>&1 | grep -q 'GNU Awk'; then
  : # Nothing to do here, GNU awk exists
elif "$AWK" --version 2>&1 | grep -q 'awk version \d*'; then
  AWK="$(command -v gawk)"
  if ! "$AWK" --version 2>&1 | grep -q 'GNU Awk'; then
    echo "GNU awk not installed on this system, exiting"
    exit 1
  fi
else
  if [ "$AWK" ]; then
    echo "Unrecognized version of awk at $AWK"
  else
    echo "GNU awk not installed on this system, exiting"
  fi
  exit 1
fi

[ ! "$Delim" ] && Delim=',' # Default delimiter

# Prompt user for table to dump the data into (by default, use CSV filename without the extension)
if [ ! "$Table" ]; then
  temp="$(echo "$CsvFile" | sed 's/\..*//g')" # Get CSV file's name without the extension
  read -p "Enter a name for this table when dumping into MySQL (leave blank to use table name '$temp'): "  Table
  [ ! "$Table" ] && Table="$temp"
fi
echo "Using table name '$Table' when dumping into MySQL"

# Read headers of csv file into $columns array
OIFS=$IFS
IFS="$Delim"
columns=($(head -1 "$CsvFile"))
IFS=$OIFS

# Define array of available SQL types
VARCHAR='VARCHAR(255)'
DATETIME='DATETIME'
INT='INT'
DECIMAL_10_2='DECIMAL(10,2)'
DECIMAL_27_18='DECIMAL(27,18)'
BOOLEAN='BOOLEAN'
MEDIUMTEXT='MEDIUMTEXT'
TEXT='TEXT'
STR_TO_DATE='STR_TO_DATE()'
NULLIF='NULLIF()'
CUSTOM='CUSTOM'
sqltypes=("$VARCHAR" "$DATETIME" "$INT" "$DECIMAL_10_2" "$DECIMAL_27_18" "$BOOLEAN" "$MEDIUMTEXT" "$TEXT" "$CUSTOM")

# Initialize more arrays
coltypes=() # column types
coldatefmt=() # STR_TO_DATE() format strings for each column, only applicable for DATETIME type columns (optional)
colnullif=() # custom value for each column to be treated as 'NULL' (optional)
DATEREF=$(cat <<HEREDOC
   %Y Year, numeric, four digits
   %y Year, numeric (two digits)
   %c Month, numeric (0..12)
   %m Month, numeric (00..12)
   %M Month name (January..December)
   %b Abbreviated month name (Jan..Dec)
   %d Day of the month, numeric (00..31)
   %e Day of the month, numeric (0..31)
   %D Day of the month with English suffix (0th, 1st, 2nd, 3rd, ?-)
HEREDOC
) # STR_TO_DATE() format string reference

# Loop over each table column and prompt user for corresponding type
for ((i=0; i<${#columns[@]}; i++)); do
  # Print the top 15 values for column
  echo
  "$AWK" -vFPAT='([^'"$Delim"']*)|("[^"]+")' -vOFS="$Delim" 'NR<=15{print $'$((i+1))'}' "$CsvFile"
  echo

  # Prompt user to select SQL type for column
  PS3="Select an SQL type for column '${columns[$i]}' in table '$Table': "
  select sqltype in "${sqltypes[@]}"; do
    ([ "$sqltype" ] || echo "${sqltypes[@]}" | grep "$REPLY" >/dev/null 2>&1) && break
    echo "Invalid option $REPLY"
  done
  [ ! "$sqltype" ] && sqltype="$REPLY"
  case "$sqltype" in
    "$STR_TO_DATE")
      echo "$DATEREF"
      sqltype="$DATETIME"
      read -p "Enter the custom date format for this column (without quotes): "
      coldatefmt[$i]="$REPLY"
      ;;
    "$NULLIF")
      read -p "Enter the type for this column: "
      sqltype="$REPLY"
      read -p "Enter the value to be treated as NULL (strings must be single-quoted): "
      colnullif[$i]="$REPLY"
      ;;
    "$CUSTOM")
      read -p "Enter the custom type for this column: "
      sqltype="$REPLY"
      ;;
  esac
  echo "Setting column '$(awk -F, "NR==1{print \$$((i+1))}" "$CsvFile")' to type $sqltype"
  coltypes[$i]="$sqltype"
done

# Assemble user-populated $coltypes array into a CREATE TABLE and LOAD DATA INFILE sql command
create_table="CREATE TABLE IF NOT EXISTS $Table (${columns[0]} ${coltypes[0]}" # SQL query to create table based on csv headers
load_data=$(cat <<HEREDOC
LOAD DATA INFILE "$PWD/$CsvFile"
INTO TABLE $Table
COLUMNS TERMINATED BY '$Delim'
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\b'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
HEREDOC
)
colnames=(${columns[0]}) # array of column names/ variables for $load_data command
colset=() # array of SET 'key = value's for $load_data command
for ((i=1; i<${#columns[@]}; i++)); do
  colname="${columns[$i]}"
  coltype="${coltypes[$i]}"
  create_table="$create_table, $colname $coltype" # append <column_name>, <column_type> to $create_table string
  if [ "${coldatefmt[$i]}" ]; then
    colnames[$i]="@$colname"
    colset+=("$colname = STR_TO_DATE(NULLIF(@$colname, ${colnullif[$i]}), '${coldatefmt[$i]}')")
  else
    case "$coltype" in
      $VARCHAR|$TEXT|$MEDIUMTEXT)
        colnames[$i]="$colname"
        ;;
      *) # Non-text column types should be null if input value is an empty string
        colnames[$i]="@$colname"
        colset+=("$colname = NULLIF(@$colname, '${colnullif[$i]}')")
        ;;
    esac
  fi
done
create_table="$create_table);"
# join array elements with a sep(arator)
# $1 is return variable name
# $2 is sep
# $3... are the elements to join
#
# Usage:
#    a=( one two "three three" four five )
#    join result ", " "${a[@]}"
#    echo $result
join() {
    local retname=$1 sep=$2 ret=$3
    shift 3 || shift $(($#))
    printf -v "$retname" "%s" "$ret${@/#/$sep}"
}
join colnames_joined ', ' "${colnames[@]}"
join colset_joined ', ' "${colset[@]}"
if [ "${#colset[@]}" -eq 0 ]; then
  load_data="$load_data
($colnames_joined)
;"
else
  load_data="$load_data
($colnames_joined)
SET $colset_joined
;"
fi
echo
echo "$create_table" # show user final $create_table command
echo
echo "$load_data" # show user final $load_data command
echo

if [ ! "$Execute" ]; then
  read -p "Load commands into MySQL (y/n)? "
  [ "$REPLY" = 'y' ] && Execute='true'
fi

# echo sql string into MySQL
echo_sql() {
  if [ ! "$Db" ]; then read -rp "Enter your MySQL database name: "; Db="$REPLY"; fi
  if [ ! "$User" ]; then read -rp "Enter your MySQL username: "; User="$REPLY"; fi
  if [ ! "$Pass" ]; then read -rp "Enter your MySQL password: "; Pass="$REPLY"; fi
  echo "$1" | mysql -v -u"$User" -p"$Pass" -D "$Db"
  return
}

# Execute both $create_table and $load_data queries in MySQL
if [ "$Execute" ]; then
  echo_sql "$create_table"; [ $? -ne 0 ] && exit
  echo_sql "$load_data"; [ $? -ne 0 ] && exit
fi