FREE: Bash script to convert MySQL tables into Spreadsheets

[ryandward]

This is a BASH script, I am going to write a Python version sometime next week. I'd be happy if someone critiqued me even

 

I wrote this for work, because there are some guys who have to import the tables we save on MySQL as spreadsheets, for programs like ARCGIS. I am sure there are tons more that could benefit from using MySQL data in a different format. Without further adieu, this is my script I have been working on this evening, and it is VERY helpful. It converts entire tables into Excel readable formats, with no knowledge or effort required to operate.

 

#!/bin/bash
###Convert MySQL files into Excel Files###

#Get user info
echo -e "Enter MySQL user name..." 
read User

echo -e "Enter MySQL password..." 
read -s Password

#Authentication successful?
export Valid=`mysql -u $User -e "show databases;" -p$Password`

if [ "$Valid" == "" ]
then exit
fi 

echo -e ""
echo -e ". . . . . ."

###FOR DATABASES###

#Display databases
echo -e "Displaying a list of databases on the system"

mysql -u $User -e "show databases;" -p$Password

#Ask user to pick database
echo -e "Which of these databases would you like to use? Or escape [x]"
read Dbu

	if [ "$Dbu" == "x" ]
	then	echo "... See ya!"
		exit
	fi

#Check to see if database exists
export ExistDbu=`mysql -u$User -p$Password -Bse 'show databases'| egrep -c -x $Dbu`
echo ". . . . . ."
echo "Found $ExistDbu exact match for $Dbu."
echo ". . . . . ."

#Prompt user to fix error if the selection is invalid

while [ "$ExistDbu" == "0" ]
do
echo "Pick a valid database, or escape [x]."
read Dbu

	if [ "$Dbu" == "x" ]
	then	echo "... See ya!"
		exit
	fi	

export ExistDbu=`mysql -u$User -p$Password -Bse 'show databases'| egrep -c -x $Dbu`
echo "Found $ExistDbu exact match for $Dbu."

done

###FOR TABLES###

#Display Tables
echo -e "Displaying a list of tables on the $Dbu database"

mysql -u $User -e "show tables in $Dbu;" -p$Password

#Which table?	
echo -e "Which of these tables would you like to use? Or escape [x]"
read Table

	if [ "$Table" == "x" ]
	then	echo "... See ya!"
		exit
	fi

#Check to see if Table exists
export ExistTable=`mysql -u $User -e "show tables in $Dbu;" -p$Password| egrep -c -x $Table`
echo ". . . . . ."
echo "Found $ExistTable exact match for $Table."
echo ". . . . . ."

#Prompt user to fix error if the selection is invalid

while [ "$ExistTable" == "0" ]
do
echo "Pick a valid table, or escape [x]."
read Table

	if [ "$Table" == "x" ]
	then	echo "... See ya!"
		exit
	fi	

export ExistTable=`mysql -u $User -e "show tables in $Dbu;" -p$Password| egrep -c -x $Table`
echo "Found $ExistTable exact match for $Table."

done

###NAMING CSV###

#Reuqest name for CSV
echo -e "Below is a list of currently existing files"
ls ~/Documents
echo ". . . . . ."
echo -e "What would you like to name the CSV? Or escape [x]"
read Csv

	if [ "$Csv" == "x" ]
	then	echo "... See ya!"
		exit
	fi		

LocFile=~/Documents/$Csv.csv

while	 [ -a $LocFile ]
do
echo -e "That file name already exists, please pick a new name, or escape [x]."
echo -e ". . . . . . ."
read Csv

		if [ "$Csv" == "x" ]
		then	echo "... See ya!"
		exit
		fi

LocFile=~/Documents/$Csv.csv

done

echo "Writing table $Table from the $Dbu database."
mysql -u $User $Dbu -B -e "select * from \`$Table\`;" -p$Password | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > ~/Documents/$Csv.csv
echo "Your new file is located at $LocFile"