Excel to SQL: How do I do it?

When I had to collect a small set of data what I usually do is crank up an Excel sheet. By the time I had collected a week worth of data the excel sheet was having more columns than you can imagine. Then one Saturday morning I had a thought “hmmm… seems I should have made a database on the first day”. I opened up Navicat Lite and created couple of tables. Viola… database done. Now just copy and paste my Excel columns job done. At least that’s what I thought in the first place. As always, practically it wasn’t as simple as that. The excel columns had the date format as January 28, 2010 and the way I wanted to in the tables was 28-01-2010.

Boom! Now what to do..I was patient and explored the Navicat interface. Ahaaa, an Import wizard button. Happily as I pointed towards it realized that it was grayed out. Cursing PremiumSoft (developers of Navicat) marketing under my breath I started searching for an Excel to SQL converter. While scouring the internet i found out actually I could use the existing software to get my beloved data to SQL.

Ingredients

• Microsoft Excel sheet
• ODBC driver for the database
• Microsoft Access
• Database management tool (optional)

Excel sheet

Plant Name	Expense	Income	Net Income	Time(H)
Orchid	29130	32562	3432	34
Rose	25080	27892	2812	30
Daffodil	21110	23240	2130	28
Tulip	17990	19272	1282	26
Coconut	5150	8208	3058	110
Kiwifruit	4636	6960	2324	104
Grapes	4127	6972	2845	105.6

ODBC driver download

MySQL – http://dev.mysql.com/downloads/connector/odbc/

SQL Server – Install Connectivity Components at installation

Oracle – http://www.oracle.com/technology/software/tech/windows/odbc/index.html

Database management tool (optional)

Even i cursed those marketing guys a little while ago Navicat Lite is a slick GUI tool for MySQL database. If you prefer command line that will be fine too. In that case you can omit the management tool altogether.

Now lets cook SQL soup

• Go ahead and fire up Access and create an empty database.
• Select the Excel button in the Import area of the External Data tab.

• Select the path to the Excel file and select one of the options which is self explanatory.
• Then open up Control Panel->Administrative Tools->Data Sources (ODBC).

• Double click on your ODBC connection and connect to the database that is going to have the table.
• Then select More->ODBC Database in the Export area of the External Data tab.

• Enter the table name you want to appear in the database connected earlier & click OK

Plant Name	Expense	Income	Net Income	Time
Coconut	5150	8208	3058	110
Daffodil	21110	23240	2130	28
Grapes	4127	6972	2845	105.6
Kiwifruit	4636	6960	2324	104
Orchid	29130	32562	3432	34
Rose	25080	27892	2812	30
Tulip	17990	19272	1282	26

Viola.. now feeling satisfied of getting all those Excel data to SQL I could go ahead and begin the actual development of an application.