Networking

Unix and Linux network configuration. Multiple network interfaces. Bridged NICs. High-availability network configurations.

Applications

Reviews of latest Unix and Linux software. Helpful tips for application support admins. Automating application support.

Data

Disk partitioning, filesystems, directories, and files. Volume management, logical volumes, HA filesystems. Backups and disaster recovery.

Monitoring

Distributed server monitoring. Server performance and capacity planning. Monitoring applications, network status and user activity.

Commands & Shells

Cool Unix shell commands and options. Command-line tools and application. Things every Unix sysadmin needs to know.

Home » Commands & Shells, MySQL

Exporting spreadsheet data to MySQL

Submitted by on December 9, 2005 – 2:10 am 2 Comments

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:

Excel spreadsheet

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 ->

Excel spreadsheet

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:

Excel spreadsheet

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:

Excel spreadsheet

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
Print Friendly, PDF & Email

2 Comments »

  • jdubdoubleu7704 says:

    I’ve got data in an Excel spreadsheet with one worksheet, that I want to use to populate a normalized MySQL database with multiple tables. There is no one-to-one mapping of the spreadsheet columns and rows to the multiple tables. Short of cutting and pasting all the data in from Excel to various MySQL tables, or writing a Perl script from scratch, does anyone know a good tool that would help do this, or a sample script I could customize for my own needs?

  • Kobe says:

    I am stupid about computers. I had a spreadsheet program with Windows 98 and XP. I found a word processing thing with Wordpad, but cannot seem to find anything concerning spreadsheets. Do I need to buy something else to get a spreadsheet program/

Leave a Reply

%d bloggers like this: