Importing Data from Spreadsheet (XLS) to SQL Database

• Download source - 27.14 KB

Introduction

Sometimes people in a corporate environment change information between teams using a spreadsheet. Despite the fact this is a really useful approach between non-technical users, I found myself several times with the boring work to transfer spreadsheets data to some SQL database.

With the proper software aid, this can be done very easily. But, what if you have to do this work in a disconnected environment without any fancy software features to help? To avoid a simple task to turn to a very time consuming one, you can carry this utility everywhere, just in case.

What this piece of code does is automate the work required to import spreadsheet cells data to any database table. All you have to do is:

1. Save the spreadsheet as CSV format file
2. Map cells to new or existing data table columns
3. Generate the code

A handy feature included provides the way to save spreadsheet cells to table columns mapping information, or "the schema", to make life easier if you need to repeat the script conversion of the same file or to use other files with the same data structure.

User Interface

See the following example to figure out how to use this app.

Step 1: Save the Spreadsheet as CSV Format File

Create a blank spreadsheet and write on the first three cells: FRIEND_NAME (A1), BIRTH_DATE (A2), EMAIL (A3). To put some meaningful content, copy the following snippet or download all files required here.

FRIEND_NAME	BIRTH_DATE	EMAIL
JANE SKULLFOOT	1980-03-05	janefoot@unamedomain.com
FRANK FINGERKNOCK	1970-11-30	frkfgr@tester.com
MATTHEW PINLUNK	1975-05-04	mpinlunk@mydomain.com
JILL THIMBLEAIR	1987-01-02	jllair@nodomain.com

Now, save this file in ".csv" format. If everything went just fine, you must have a file with this content:

FRIEND_NAME;BIRTH_DATE;EMAIL
JANE SKULLFOOT;05/03/1980;janefoot@unamedomain.com
FRANK FINGERKNOCK;30/11/1970;frkfgr@tester.com
MATTHEW PINLUNK;04/05/1975;mpinlunk@mydomain.com
JILL THIMBLEAIR;02/01/1987;jllair@nodomain.com

Step 2: Map Cells to New or Existing Data Table Columns

Execute the application and click on "Load file" and select the ".csv" file you previously created. The file content will not be validated.

Supposing you have created a table with the following structure:

CREATE TABLE FRIEND_LIST
(
FRIEND_NAME VARCHAR(100) NOT NULL,
BIRTH_DATE DATETIME,
EMAIL VARCHAR(100) NOT NULL
)

To associate each to column from spreadsheet to each field in the destination table, click on "Fields..." button in the main form. This will open up a dialog where you might write table fields names in the exact appearance sequence of the corresponding data in the ".csv" file.

You can save this mapping by clicking on "Save..." button.

Step 3: Generate the Code

If you have gotten to this point, all that is left to do is click on "Generate" button. The schema and the parameters "Separator", "Trim spaces" and "Use first line to read field names" will be applied during script generation. When processing completes, you will see the following scripts:

INSERT INTO FRIEND_LIST (FRIEND_NAME, BIRTH_DATE, EMAIL) _
	VALUES ('JANE SKULLFOOT','1980-03-05','janefoot@unamedomain.com');
INSERT INTO FRIEND_LIST (FRIEND_NAME, BIRTH_DATE, EMAIL) _
	VALUES ('FRANK FINGERKNOCK','1970-11-30','frkfgr@tester.com');
INSERT INTO FRIEND_LIST (FRIEND_NAME, BIRTH_DATE, EMAIL) _
	VALUES ('MATTHEW PINLUNK','1975-05-04','mpinlunk@mydomain.com');
INSERT INTO FRIEND_LIST (FRIEND_NAME, BIRTH_DATE, EMAIL) _
	VALUES ('JILL THIMBLEAIR','1987-01-02','jllair@nodomain.com');

Conclusion

Well, this is the first working version of a simple utility that requires nothing special to run and is very time-saving. I hope it helps you someday.

Enjoy, improve and share.