ryandward Posted April 28, 2011 Share Posted April 28, 2011 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" Link to comment https://forums.phpfreaks.com/topic/234945-free-bash-script-to-convert-mysql-tables-into-spreadsheets/ Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.