Link to home
Start Free TrialLog in
Avatar of m3
m3

asked on

Easy and urgent

I need to import some data from a flat file (excel) to an Oracle DataBase, I have no clue about Oracle, may be some SQL. I may also need to batch loading and create a new database. Any help will be appreciated.
Avatar of dslavin
dslavin

Here are two possibilities:

1) SQL*Loader:  This is an Oracle product that was created for just this purpose.  I haven't used it so I can't help you with syntax, but if you have Oracle documentation or can get a book on this, or go to an Oracle help site, then you can probably get it to work.

2) TEXTIO:  Oracle has some stored procedures which you get standard with all Oracle databases.  One of these is TEXTIO which lets you read/write to flat files.  You would need to include calls to this is a SQL*Plus session or PL/SQL stored procedure.  The full syntax is in Oracle's Server > PL/SQL guide.
erm...
I think TEXT_IO is specific to Oracle forms, for server side PL/SQL (stored procedures) you want to use UTL_FILE.

I would recommend SQL*Loader, the scripts are pretty simple and it's very easy to get excel spreadsheets into the database.  Basically

1) create a table in your database using the same columns as exist in the spreadsheet (make sure the data types are appropriate).
2) Write your SQL*Loader script
3) Save your excel spreadsheet as a CSV file (not this can only do one work sheet per file)
4) run the script to import the data into the database.

If you post some sample records from your spreadsheet I could write you a SQL*Loader script and table definition that should get you started.

Vlad.
Sorry about that.  I always get TEXT_IO mixed up with UTL_FILE.
ASKER CERTIFIED SOLUTION
Avatar of chiche
chiche
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of m3

ASKER

It's a good idea to use Access, but I would like to be able to batch processing, since I have to load the same tables every day. An other problem is that my tables already exist in Oracle and some time I am overwriting them some time I just add some rows, and I couldn't figure out a way to add rows.
Vlad, could give me a sample script for a table with 5 rows, lets say ID = CHARVAR2:30, Price = NUMBER:17,8, Name = VARCHAR2:30, ID2 = CHARVAR2:30, VAL = NUMBER:20,3?
Thanks,
m3.
Avatar of m3

ASKER

It works, but I got a problem when trying to link or import a table from Oracle into access, I am getting the following message;
There are several tables with that name. Please specify owner in the format 'owner.table'
Note: I can import the table to XL but I have too many rows to fit in one file.
Any help?
Thanks.
Avatar of m3

ASKER

The last problem can be easily solved by choosing only tables named owner.table on the list box displyed by access.