The following is a process for migrating spreadsheet data to MySQL database using a Shell script in a Unix environment. To follow this tutorial you must already know how to create MySQL schemas and tables. You must also have basic Unix skills.

Consider the following task:

You have an Excel spreadsheet with a table containing data:

The first step is to export this data to a character-separated format. The common format is comma-separated CSV file. However, using comma as a field separator is not a good idea because text in your spreadsheet is likely to contain commas. This will make it complicated to parse text.

You need to chose a field separator other than comma. To do so in Windows click Start -> Settings -> Control Panel -> Regional and Language Options -> Customize ->

In the “List separator” field enter any single character that’s not present in your spreadsheet. Something like @ often works well (unless your data contains e-mail addresses). Click OK twice

In Excel click File -> Save As -> select CSV format:

Now you have a text file that looks something like this:

First Name@Last Name@Address@City@State@ZIP
Alex@Smith@213 Penn St.@Newark@NJ@21822
William@O'Connor@12 Summer St.@Wilmington@DE@19703
John@Mackin@18 Washington Dr.@Baltimore@MD@21872

Delete the first line of this file containing column headers. Use MySQL Administrator GUI to create a table in your database that would match the structure of this file. Remove all spaces from the column names, make them lower-case, and add unique ID column with auto increment:

Below is an example of a Shell script that will read your @-separated text.csv file and insert into the MySQL table you just created.

Input file name: text.csv
Database name: database
Table name: table1

#!/bin/sh

MYSQL="/usr/bin/mysql"
DBUSER="your_username"
DBPASS="your_password"
DBNAME="database"
LINES=$(cat text.csv | wc -l | awk '{print $1}')
i=1
cat text.csv | while read LINE
do
	first_name=$(echo "$LINE" | awk -F'@' '{print $1}' | sed "s+'+\'+g")
	last_name=$(echo "$LINE" | awk -F'@' '{print $2}' | sed "s+'+\'+g")
	address=$(echo "$LINE" | awk -F'@' '{print $3}' | sed "s+'+\'+g")
	city=$(echo "$LINE" | awk -F'@' '{print $4}' | sed "s+'+\'+g")
	state=$(echo "$LINE" | awk -F'@' '{print $5}' | sed "s+'+\'+g")
	zip=$(echo "$LINE" | awk -F'@' '{print $6}' | sed "s+'+\'+g")


	echo "$i of ${LINES}: $first_name $last_name"
	(( i = i + 1 ))

	$MYSQL -u$DBUSER -p$DBPASS $DBNAME << EOF
	INSERT INTO

        table1(first_name,last_name,address,city,state,zip)
        values('$first_name','$last_name','$address','$city','$state','$zip');
EOF
done