Convert XLSX to a MySQL Table

Originally published January 21, 2021 @ 1:54 pm

Some people find it hard to let go of Excel even when the situation is ripe for a database. And so dozens of different versions of the same spreadsheet edited by many people are floating around Sharepoint, and nobody quite knows which one to use.

Instead of changing everyone’s mind, I found the best I can do is maintain my own version of the spreadsheet in a database table. Eventually, when people get thoroughly confused, they’ll come to me.

I needed a shell script to take an arbitrary Excel spreadsheet and convert it into a database table. And I didn’t want to create the table manually either: I wanted it to be generated dynamically based on the spreadsheet itself.

And so here it is below (and you can also get it from my GitHub repo). You can install it like so:

curl -s0 -k -o ~/excel_to_mysql.sh "https://raw.githubusercontent.com/igoros777/kw/master/excel_to_mysql.sh" && chmod 755 ~/excel_to_mysql.sh && ln -s ~/excel_to_mysql.sh /usr/bin/excel2mysql

The script relies on your spreadsheet having column headers. The script will also sanitize the input data as best as it can to work with MySQL. All the columns created will be VARCHAR(255). Additionally, you need to have unoconv installed to allow the script to convert XLSX to CSV. I have some instructions for this here.

The syntax is simple:

excel2mysql -f /path/to/spreadsheet.xlsx -d <db_name> [-u <db_user>] [-h <db_host>] [-p <db_pass>] [-t <table_name>]

And here’s the script:

#!/bin/bash
#
#                                      |
#                                  ___/"\___
#                          __________/ o \__________
#                            (I) (G) \___/ (O) (R)
#                                   Igor Os
#                               igor@igoros.com
#                               www.igoros.com
#                                 2021-01-22
# ----------------------------------------------------------------------------
# Convert a basix Excel spreadsheet into a database table
# ----------------------------------------------------------------------------

while getopts ":f:h:d:u:p:t:" OPTION
do
	case "${OPTION}" in
    f)
			datafilexls="${OPTARG}" ;;
		h)
			db_host="${OPTARG}" ;;
		d)
			db_name="${OPTARG}" ;;
    u)
  		db_user="${OPTARG}" ;;
    p)
  		db_pass="${OPTARG}" ;;
    t)
      tbl_name="${OPTARG}" ;;
		\? ) echo "Unknown option: -$OPTARG" >&2; usage;;
    :  ) echo "Missing option argument for -$OPTARG" >&2; usage;;
    *  ) echo "Unimplemented option: -$OPTARG" >&2; usage;;
	esac
done

help() {
	this_script=$(basename "$(test -L "$0" && readlink "$0" || echo "$0")")
cat << EOF
	SYNTAX:
		${this_script} -f /path/to/spreadsheet.xlsx -d <db_name> [-u <db_user>] [-h <db_host>] [-p <db_pass>] [-t <table_name>]
EOF
}

configure() {
  if [ -z "${datafilexls}" ] || [ ! -f "${datafilexls}" ]; then
    help
    exit 11
  else
    datafile="${datafilexls%.*}.csv"
  fi
	if [ ! "${db_name}" ]; then
		help
		exit 15
	fi
	if [ ! "${db_user}" ]; then
		db_user="$(whoami)"
	fi
	if [ ! "${db_host}" ]; then
		db_host="localhost"
	fi
	if [ ! "${db_pass}" ]; then
		echo -n "Enter password for ${db_user}: "
		read -s db_pass
		echo
		if [ ! "${db_pass}" ]; then
			help
			exit 19
		fi
	fi
  if [ ! "${tbl_name}" ]; then
    tbl_name="$(basename "${datafilexls}" | sed -e 's/\./_/g' -e 's/\-/_/g' -e 's/ /_//g')"
    echo "Data will be loaded into ${tbl_name}"
  fi
  tmpdir="/var/tmp"
  if [ ! -d "${tmpdir}" ] ; then mkdir "${tmpdir}" ; fi
  table_create_sql="${tmpdir}/${this_script}_table_create.sql"
  if [ -f "${table_create_sql}" ]; then /bin/rm -f "${table_create_sql}"; fi
  table_load_sql="${tmpdir}/${this_script}_table_load.sql"
  if [ -f "${table_load_sql}" ]; then /bin/rm -f "${table_load_sql}"; fi
  MYSQL="/usr/bin/mysql --batch --skip-column-names --max_allowed_packet=100M -h${db_host} -u${db_user} -p${db_pass} ${db_name} -e"
  MYSQL2="/usr/bin/mysql --batch --skip-column-names --max_allowed_packet=100M -h${db_host} -u${db_user} -p${db_pass} ${db_name}"
}

xls_convert() {
  unoconv -i FilterOptions=44,34,76,2,1/5/2/1/3/1/4/1 -f csv -d spreadsheet -o "${datafile}" "${datafilexls}"
  if [ ! -f "${datafile}" ]; then
    echo "Unable to convert "${datafilexls}" to CSV. Exiting..."
    exit 22
  fi
}

table_drop() {
  ${MYSQL} "DROP TABLE ${tbl_name};"
}

table_create() {
cat << EOF > "${table_create_sql}"
CREATE TABLE ${tbl_name} (
\`id\` INT(11) NOT NULL AUTO_INCREMENT,
  $(i=1; head -1 "${datafile}" | tr , '\n' | sed -r 's/ /_/g' | sed -r 's/[\(\)]//g' | \
  sed -e 's/\(.*\)/\L/' | sed 's@/@_@g' | sed 's/->/to/g' | sed 's/ $//g' | while read line
  do
    if [ ! -z "${line}" ]
    then
      echo "\`${line}\` VARCHAR(255),"
    else
      echo "\`field_${i}\` VARCHAR(255),"
    fi
    (( i = i + 1 ))
  done)
PRIMARY KEY (\`id\`),
UNIQUE INDEX \`id_UNIQUE\` (\`id\` ASC))
ENGINE = MyISAM ;;
EOF

${MYSQL2} < "${table_create_sql}"
}

data_load() {
cat << EOF2 > "${table_load_sql}"
LOAD DATA LOCAL INFILE '${datafile}' INTO TABLE ${tbl_name} \
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES \
(\
$(i=1; head -1 "${datafile}" | tr , '\n' | sed -r 's/ /_/g' | sed -r 's/[\(\)]//g' | \
sed -e 's/\(.*\)/\L/' | sed 's@/@_@g' | sed 's/->/to/g' | sed 's/fdqn/fqdn/g' | while read line
do
if [ ! -z "${line}" ]
then
echo -n "${line}, "
else
echo -n "field_${i}, "
fi
(( i = i + 1 ))
done | sed 's/, $//g')
);
EOF2

${MYSQL2} < "${table_load_sql}"
}

# RUNTIME

configure
xls_convert
table_drop 2>/dev/null
table_create
data_load