From people new to databases for the web a question I have often been asked is, “What is an easy way to prepare data for inserting to a database table?”

If you have a spreadsheet software like Microsoft Excel or OpenOffice Calc, have you considered the power of that program to prepare data? Before you think about finding software to convert spreadsheets to MySQL, Postgre SQL or other database tables for web applications, have a look at this how-to for creating SQL INSERT statements.

Spreadsheet Tools to Prepare Data for SQL

In the above screenshot we have a simple table of employee information in a spreadsheet. How do we insert those in a database table? To work in MySQL, each of those rows has to resemble an insert statement like these:

[sql] INSERT INTO company.emp (id, fname, lname, city) VALUES ( 1, ‘Ed’, ‘Brown’, ‘Ste-Therese’); INSERT INTO company.emp (id, fname, lname, city) VALUES ( 2, ‘Oscar’, ‘Madison’, ‘Chambly’); INSERT INTO company.emp (id, fname, lname, city) VALUES ( 3, ‘Felix’, ‘Unger’, ‘Pointe-Claire’); [/sql]

Using string concatenation it is possible to create statements to insert each of those rows to a MySQL table. How does each one of those statements differ? All that is distinct are the values for id, fname, lname and city. If we can come up with a text equation to change those values for each of the rows, the task of creating an insert statement for each row of data will be so much easier.

Breaking Down a Text Equation

There are two main components for text equations in spreadsheets:

• text that is entered by the keyboard
  • this data is wrapped in double-quotes (“) with an equal sign (=) at the beginning
• data that is picked-up from cells in the spreadsheet
  • to instruct the software that data from cells is being called, the ampersand (&) is used to mark the beginning and end of such data

The screenshot below illustrates the text equation that results in the SQL expression:

[sql] INSERT INTO company.emp (id, fname, lname, city) VALUES ( 1, ‘Ed ‘, ‘Brown’, ‘Ste-Therese’); [/sql]

Expressed another way, the text illustration of the SQL statement highlights the variable parts of the text equation in white on black whereas the constant parts of the equation are in blue on grey.

Once the equation is returning desired results, copy and paste from the first row to all other rows in the spreadsheet.

Gee, this is great… Now what do I do?

With all the queries formed and reviewed by you as correct – yes, check your work – the next steps are fairly simple.

1. As shown in the above screenshot, select all the rows with the query statements. Only those with the statements, not the data to the left of them.
2. Copy and paste to a simple text editor like Notepad, Gedit, Notepad ++, etc.
3. Save the document with a txt or sql extension. For this example, we’ll call the file inserts.sql

Using MySQL at the command-line enter a command such as:

[bash]$ mysql -u[user name] -p[password]<inserts.sql[/bash]

With the appropriate user name and password submitted, MySQL will execute the statements in the inserts.sql file.

Feel free to download a copy of the sample spreadsheet so you can try this on your own.

Sample Excel (xls) file in ZIP: employee_insert_demo.zip MD5 hash: a1e1d878d68be2b3975a8f211dd0dfe9