Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Auto-Loading MS Excel data -> Oracle

Auto-Loading MS Excel data -> Oracle

From: Gary M. Greenberg <garyg_at_NoSpam.southeast.net>
Date: 1997/02/08
Message-ID: <garyg-0802971115060001@ts1-005.southeast.net>#1/1

I hope someone has some advice for this neophyte. Here's my situation:

We received lots of data in Excel spreadsheeets. Much of the data lacks any values; that is: many columns in many rows are blank. The data must be loaded into different Oracle tables so that the database can be exported and delivered to the end-users (on Solaris).

To illustrate, my Oracle table may be defined in a .sql file as:

create table t1 (
c1 number(3) PRIMARY KEY,
c2 number(3) NOT NULL,
c3 varchar2(50) NOT NULL,
c4 varchar2(10) NULL,
c5 number(10) NULL
);

Using Excel's SaveAs options:
(1) I find .prn kludgy 'cause then one must know the exact `position(n:N)'
for each element, and if the table has many columns each row is truncated and a series of files are created (I've used UNIX paste and sqlload to work around this but it's not something easily automated as each data file is very different).
(2) I find .csv also kludgy 'cause literal strings aren't quoted as
Oracle needs them and literal strings without spaces aren't quoted at all.

Before I write an ANSI C, perl, or awk program to post-process my data after outputting it as .csv, I'm wondering if:
(a) I missed something that would make life with MSloth tolerable, or
(b) someone has already written a utility to help with this type

    of conversion, or
(c) there is some other MS SaveAs option which can be directly read
into Oracle along with another way to automatically load it.

reply be post and/or email as you please. Thanks,

gary           /* GIS Applications Developer */
        (Email)?(>/dev/null):(remove NoSpam before replying)
             -=- visit The C Programmers' Reference -=-
          http://users.southeast.net/~garyg/C_ref/C/c.html
Received on Sat Feb 08 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US