Serving businesses throughout North America since 1998

Oracle, Linux and Lims - Programming, Installation, Consulting

Easy way to get data from a spreadsheet to Oracle

I've got a new tip I found on the net that I just had to share with everyone. It's a quick and easy way to get data from a spreadsheet into Oracle without the hassle of writing code.

Save the coded solutions, and there are many good ones, for the larger or repeating jobs. For those smaller or one-off jobs, this is the way to go. (My thanks go out to whomever first thought of this method, where ever you are.)

So basically, all we are going to do is use the spreadsheet to create a series of insert statements for us, then copy and paste those into our sql tool of choice.

For this example, assume a simple spreadsheet like this:

First Name, Last Name, Age, Sex
John, Doe,35,M

And a database table PEOPLE to match:

FIRST_NAME varchar2(20),
LAST_NAME varchar2(40),
AGE number,
SEX varchar2(1)

Now comes the fun part.

We have our data in columns A-D so on row 1, column E or greater, add this code:

= "insert into people values ('"&A1&"','"&B1&"','"&C1&"','"&D1&"');"

Copy and paste that code all the way down the remaining rows so that you have insert statements that look like this in column E:

insert into people values ('Bob','Jones','15','M');
insert into people values ('John','Doe','35','M');

Now copy and paste column E into sql*plus, SQL Developer or your favorite tool. Execute the statements and you are done!

Quick and easy! - Copyright (c) 2011 - Scott Johnston Inc. All rights reserved.