Click here to Skip to main content
Click here to Skip to main content

Tagged as

Importing Data from Spreadsheet (XLS) to SQL Database

, 27 May 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
CSV 2 SQL Script Generator

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.

image1.jpg

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

caiubyfreitas
Software Developer (Senior)
Brazil Brazil
Caiuby Freitas has been working in IT industry for the last 15 years and is specialized in sofware modelling and business analysis.

Comments and Discussions

 
QuestionWhy XLS in title? It should be CSV PinmemberJohnny Glenn18-Apr-12 23:52 
GeneralMy vote of 2 PinmemberFeng Liu6-Oct-11 7:40 
GeneralMore simple way Pinmemberbfx1-Jun-10 19:44 
GeneralCaution PinmemberDave Vroman1-Jun-10 8:03 
GeneralBCP PinmvpPIEBALDconsult28-May-10 5:29 
GeneralEver more Simpler way to get the Job Done - Use Microsoft Office Access Projects Pinmemberarzulfi28-May-10 2:15 
GeneralINSERT INTO Pinmemberexiton327-May-10 21:49 
GeneralRe: INSERT INTO Pinmembermikemidhts31-May-10 10:46 
GeneralRe: INSERT INTO PinmemberTobiasP7-Jan-11 0:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141216.1 | Last Updated 27 May 2010
Article Copyright 2010 by caiubyfreitas
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid