Re: excel to oracle

From: Tim B <nospam_at_someisp.ca>
Date: Mon, 08 Oct 2007 17:37:03 GMT
Message-ID: <3VtOi.3123$1y4.2026@pd7urf2no>

"Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message news:1191669293.305305.13870_at_o80g2000hse.googlegroups.com...
> On Oct 5, 9:16 pm, "Tim B" <nos..._at_someisp.ca> wrote:
> > "Charles Hooper" <hooperc2..._at_yahoo.com> wrote in message
> >
> > news:1191597379.577870.194270_at_r29g2000hsg.googlegroups.com...
> > > On Oct 5, 10:37 am, "Tim B" <nos..._at_someisp.ca> wrote:
> > > > I have some data in an Excel spreadsheet - about 100 -200 records -
that
> > I
> > > > need to import into Oracle, likely with some transformation
required. I
> > can
> > > > convert it to csv or xml via MS Access and then set it up as an
external
> > > > table and proceed from there. What other ways could you suggest that
> > might
> > > > be feasible for this task? Is there another way to do it with xml as
the
> > > > source?
> >
> > > If you have Access, the easiest way to do this is to:
> > > 1. Connect to the Excel spreadsheet as an external table source in
> > > Excel
> > > 2. Create a query in Access to retrieve all rows from the Excel
> > > spreadsheet and rename the columns so that they match the Oracle
> > > table.
> > > 3. Create an external table source connection to the Oracle table
> > > (through an ODBC connection).
> > > 4. Modify the query definition so that it is an append query, and
> > > select the connection to the Oracle table.
> > > 5. Run the query.
> >
> > > Much easier than it is to explain.
> >
> > Well, today I found out that for every record I import from my
spreadsheet I
> > will need to add a record to an additional table, and I'll have to deal
with
> > a couple of sequences. What I'm likely going to do is import the
spreadsheet
> > into Access and use some VBA to read the records, create the needed
insert
> > statements, and save them to a text file. I'll keep your solution in
mind
> > for when I have a more straightforward import to do. Thanks.
> >
> > Tim B

>

> Expanding on a suggestion by Malcolm Dew-Jones - just do it all in
> Excel. Following is a code sample that I produced a while ago to show
> someone how to select from and update an Oracle database based on the
> values in an Excel spreadsheet. Word wrapping will make this a little
> ugly, but each line is indented, so it should be easy to undo the word
> wrapping.

>

> Macro security must be medium or low to access the macro. The macro
> code looks like this:

<snip useful code example>
Thanks for the example code, it will save me some time if end up doing it all in Excel. Received on Mon Oct 08 2007 - 12:37:03 CDT