Skip to Main Content
  • Questions
  • Automate loading of data from Excel spreadsheets into database

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, AC.

Asked: May 26, 2000 - 12:52 am UTC

Last updated: March 07, 2009 - 1:39 pm UTC

Version: 7.3

Viewed 50K+ times! This question is

You Asked

Hi Tom,

Is there a any way whereby I can automate the loading of data
from an excel spreadsheet into my database?
The only way we know is to manually export the data into an ascii
file and then load it into the database using sqlload.

My database is sitting on a Netware server.

Thanking you in advance.

and we said...

There are a couple of ways to approach this.

1) load the excel spreadsheet into the database "as is". You can use interMedia text to convert the .xls file into a .htm file (HTML) or use iFS (see </code> http://technet.oracle.com/
for more info on that) to parse it as well.  InterMedia text will convert your XLS spreadsheet into a big HTML table (easy to parse out what you need at that point)

2) Using OLE automation, a program you write can interact with Excel, request data from a spreadsheet, and insert it.  Oracle Forms is capable of doing this for example as is other languages environments.  In this fashion, you can remove the "manual" and "sqlldr" parts -- your program can automatically insert the data.

3) You can write a VB script that uses ODBC or Oracle Objects for OLE (OO4O) in Excel.  This VB script would be able to put selected data from the spreadsheet into the database.  We would recommend OO4O.  It provides an In-Process COM Automation Server that provides a set of COM Automation interfaces/objects for connecting to Oracle database servers, executing queries and managing the results.  OO4O is available from 
http://technet.oracle.com <code>


Rating

  (51 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Automating Excel to Form

Sara Swartz, July 03, 2001 - 9:51 am UTC

I was directed here from another forum in order to find how to import the data from an excel spread sheet to a form. There is no link included that will show you how to do this. In fact, the only links were to the front page of the Oracle Technology network. This is the same as a kid wanting to know about asteroids and you drop her off in front of the city library. Thanks, but I already know about the OTN, and I know that finding anything in it is simply a matter of luck. The least you could have done was provided a link to the specific page that held the information.

Access Database to Oracle

Munz, June 06, 2002 - 11:58 am UTC

Tom:

I have an access table that I am trying to take to oracle 8.1.7. I export the table in access to ODBC database using a data source I create for my oracle database and the DataDirect 4.1 Oracle Driver ( I also tried microsoft ODBC for oracle).

The export happens and then When I go to oracle, I see the table when I do "Select table_name from user_tables".

However when I do "desc table" or "select from table" it tells me that table does not exist.

I cannot even delete the table.

Do you know what is happening?

Tom Kyte
June 06, 2002 - 4:31 pm UTC

is the table name in mixed case? if so, make sure to use quoted identifiers

desc "tableName"
select * from "tableName"


Access database

Munz, June 06, 2002 - 9:18 pm UTC

Tom:

Yes I found that table name is in lowercase. I re-did this from access and named table uppercase.

this is weird. I guess when I do "desc table" it is always looking for uppercase table.

1. Can i change table name from lowercase to uppercase.

2. to delete it shall i do "drop table "emp" ";

3. Can I use Oracle Migration Assistant to move data from all kind of databases.

4. I installed oracle ODBC driver for 8.1.7 database. However when I try to create a data source I can not see it listed in the list of drivers available in the control panel. Do you have to copy files to a certain directory in windows?

THank you

Tom Kyte
June 06, 2002 - 9:36 pm UTC

Well, in ANSI standard sql -- identifiers are case insensitive.

In sqlserver "standard" they are case sensitive.

We are not looking for "uppercase" in as much as they stored it in a particular case.

1) one method:

create view tableName as select * from "tableName"

otherwise, create the table in UPPERCASE in their stuff....

2) to delete, use 'delete from "emp"'. to DROP -- very different from delete, use 'drop table "emp"'

3) there is the migration assistant with the "upgrade process" and there is the migration workbench with a tool to migrate from sqlserver/informix/db2/etc to Oracle.

The migration toolkit/workbench -- you get that for a database version/release. We have many toolkits for you to use. You need to get the toolkit that applies.

4) no idea, never used odbc in my life -- yet. sorry.

Excel to Oracle

Iain, June 07, 2002 - 4:33 am UTC

For Question 4.

Ensure the <Oracle Home Path>\bin is set in environment path.
Alternatively.....more a quick fix than a proper solution

Copy the SQORA32.DLL (not sure if same in 8.1.7) from <Oracle Home Path>\bin to the Windows\system32 directory

Oracle Workbench

Monz, June 07, 2002 - 10:26 pm UTC

Tom:

1. If you are converting an INFORMIX or SYBASE database, woudl the workbench re-create all objects (i.e. tables, indexes, primary keys, foreign keys, sequences, stored procedures.?

2. WOuld it convert the informix stored procedure to a PL/SQL stored procedure.

Tom Kyte
June 08, 2002 - 5:09 pm UTC

1) It would do "its best". tables, indexes, primary key, foreign keys, sequences - - pretty much dead on. procedures, well.... code conversion is always a tricky thing. Say it follows the 80/20 rule -- it does the rote stuff, leaving the interesting 20% for you.


2) it would "try".

informix schema

ko, September 07, 2002 - 1:04 pm UTC

Tom:

When using the workbench to migrate informix database it takes all tables and objects and create them in the oracle database under schema "informix" and not the oracle dba schema.

Since application uses the developer acount to log in I have to refer to all informix tables using schema.table.

Can you just export all objects out of informix schema and import it into devadmin schema?

But then delete the informix schema or you have to do it though unix for this?

Is there a better way of doing this?

THank you,

Tom Kyte
September 07, 2002 - 6:52 pm UTC

Well, there is probably a setting somewhere in there (haven't used the workbench personally) to set the schema -- i would have to imagine there would be but suppose there isn't.

A simple:

exp userid=informix/password owner=informix
imp userid=devadmin/password fromuser=informix touser=devadmin

will get it for you.

Saving Access table as oracle table

mo, January 08, 2003 - 9:28 am UTC

Tom:

I have a .dbf file that I opened up in Access and then saved it into oracle table using save to ODBC database.

the oracle table is getting created fine. However the table has about 1000 records and I am only getting 900. It gave me a message"Microsoft Access was unable to append all data tothe table. The contents of fields in 100 records were deleted. If data was deleted, the data you pasted or imported doesn't match the field data types or the field size property in the destination table."

I do not have an oracle table to begin with. DO you think it is the ODBC driver issue?

Any hints.

Tom Kyte
January 08, 2003 - 4:39 pm UTC

I have no clue -- I've never used ODBC in my life

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:711825134415 <code>
is an alternative, you'll be able to see what is in the dbf file (assuming it is a dbase file)

or configure the generic connectivity that comes with the database and "select * from it" in sqlplus and see what is in there (and insert into oracle_table select * from dbf_file" if you like.


Or, open a problem report with bill.gates@microsoft.com -- he might now how to get more information from access ;)


Using OLE automation

Ali, April 09, 2003 - 2:51 am UTC

I want to use your second option "Using OLE automation".
Can you give me a code example of how I can read the OLE data in my form to insert it again in my db?

Tom Kyte
April 09, 2003 - 9:41 am UTC

Nope -- never used OLE, don't have OLE... Don't even have an operating system capable of running OLE...

try otn.oracle.com under discussion forums -> developer

parse

mo, June 06, 2003 - 11:18 am UTC

Tom:

1. If you covert the .xls file to .htm what tool do you use to parse the html table?

2. i have a user spreadsheet that looks like a form. You have address info at the top and items requested at the bottom in tabular format. When I saved .xls to .csv file and I looked at the .csv there were a lot of commas in between data. Would this format be hard to convert to oracle?

Tom Kyte
June 06, 2003 - 11:32 am UTC

1) DIY (do it yourself). I do not recommend this approach, it is "hard"

2) don't know, use your knowledge of Oracle and your application to see what it would take to load it up and process it.

Excel file loading to Oracle Database

Mark, June 06, 2003 - 12:16 pm UTC

Hi,

We had the same problem. The approach we took to solve this problem, we had written a macro, which will generate insert and update statments from the excel sheet and then cut and paste those insert or update statements. Now we have migrated to Oracle 9i, so we are using external table and reading the text file into database.

I asked gates....

Microsoft Tester, June 06, 2003 - 6:06 pm UTC

<Quoting tom>
"open a problem report with bill.gates@microsoft.com -- he might now how to
get more information from access ;)
<End quote>

Well, as per ur suggestion, I asked Gates about the problem, and he says it is useless to insert the data into Oracle, why not use SQL Server ? Anyways Oracle products are highly non-user friendly.....

Happy ?!


Tom Kyte
June 06, 2003 - 8:07 pm UTC

did he give you that broken, non functioning keyboard that drops vowels, extremely important characters in this particular language as well?

probably a new "open standard" they are inventing for all of us to conform to over over in redmond I guess.


but really -- do you honestly expect me to debug access? a product that for some reason just refused to actually install on the operating system I choose to use? sorry -- no can do.

(curious, how did you ask bill? as far as I know, they don't actually do anything similar over there.... what is the URL? I do have some issues with word on linux, maybe he can help?)

A.WAHAB, June 07, 2003 - 2:43 am UTC

Ha ha that was a good answer Tom. ;)

Reading .xls from Oracle Tools

Maju Bellamin, June 08, 2003 - 3:33 am UTC

Dear Tom,

Can't we use heterogeneous connectivity to read data from a Microsoft Excel File? Though it needs little bit of configuration, it works fine with Excel, Access files.

Tom Kyte
June 08, 2003 - 9:41 am UTC

if you have odbc access to excel, sure.

More thoughts on Excel

Tyler D. Muth, June 08, 2003 - 8:08 pm UTC

This might be repeating what you did not want to do in the first place, but if you can save the Excel files as CSV, then you can read them as external tables using the technique described in:
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6611962171229 <code>

As far as COM automation, I found a lot from google on this when writing a sync between Oracle and Outlook tasks. You could even write something using windows scripting host to open all excel files in a directory, then save them as a CSV file on a Network share on your db machine. On the db side, a db job that runs every x minutes and looks for files it hasn't already imported and reads them in as an external table would work.

No matter what, I would shy away from ODBC. It's a "least common denominator" connection. You can't leverage Oracle features such as packages or stored procedures, return ID's of rows you just inserted, etc. like you can with OLE DB or Objects for OLE (OO4O). On the Microsoft Side, working with a recordset returned as an object from an OLE DB connection is much easier and much more powerful than what you can do with ODBC. Just my 2 cents.

excel

mo, June 09, 2003 - 10:19 pm UTC

Tom:

1. On an HTML form can i have the user open the excel file, save it to .csv file and then have him copy the ASCII text and paste it into text field on the form then submit the form to a pl/sql procedure which saves the text to a long column in a temp table. Then I can search for data by counting the number of commas in the record/column created and retrieve it and insert it into the oracle table I want. IS this doeable in oracle?

2. or upload the .csv file from the browser into the unix file directory and then use UTL_FILE to read lines and parse the data and insert into oracle table?

3. Why do you say parsing the HTML file is hard? Would an excel to XML conversion and then parsing to oracle be much easier?

Thanks,

Tom Kyte
June 10, 2003 - 8:04 am UTC

1) if they cut and paste it into a text field, it'll be going to a PLSQL variable of type varchar2.

there will be no temp table, it'll just be a string of UPTO 32k in size.

If, you have them save as CSV, you can have the FILE UPLOAD it to a clob. parsing the clob is very easy.

2) you can upload to a CLOB

3) hard as compared have a csv file. save it as html, parse it. tell us if the code to do that was more difficult.

excel

mo, June 10, 2003 - 5:02 pm UTC

TOm:

1.  On Option 1, the max text I can pass using pl/sql variable is 32 K. what about if there is more text. does oracle cut it and save the first 32 K or it will not pass at all?

2.  Why do you use CLOB column type and not LONG. Is it because CLOB is more like VARCHAR2 and LONG is hard to parse/manipulate?

3.  I tried to upload a file using an HTML form but the text was not saved in the table but rather the path of the file? Do you know why?



PROCEDURE TEST_SCRIPT as
begin
htp.p('<HTML>');
htp.p('<HEAD>');
htp.p('<SCRIPT Language="JavaScript1.1">');
htp.p('   <!--');
htp.p('   function doSubmit()');
tp.p('   {');

htp.p('    document.my_form.submit();');
htp.p('   }');
htp.p('   //-->');
htp.p('   </script>');
htp.p('');
htp.p('   </HEAD>');
htp.p('');
htp.p('   <BODY>');
htp.p('   <a href="javascript:doSubmit()">Click me to submit the form</a>');
htp.p('   <form action="test_insert" name=my_form method=post>');

htp.p('<P>Files:<INPUT TYPE="file" ENCTYPE="multipart/form-data"  NAME="resume" </P>');
htp.p('   </form>');
htp.p('   </BODY>');
htp.p('   </HTML>');

end;


PROCEDURE TEST_INSERT ( resume    in   varchar2 )
as
begin
    insert into test9(id,file_text) values (1,resume);
    commit;
end;


SQL> desc test9
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 ID                                           NUMBER(5)
 FILE_TEXT                                    LONG

SQL> select * from test9;

        ID
----------
FILE_TEXT
----------------------------------------------------------------------
         1
c:\ims\t1.txt
 

Tom Kyte
June 10, 2003 - 8:02 pm UTC

1) try it and see? most browsers (used to) limit you to 30,000 characters in a text field so it was moot.

I gotta feeling that it just gets truncated. anything that could forseeably even approach 32k should be a FILE UPLOAD.

2) (note: should have said BLOB, not CLOB) long/long raw as database column types are deprecated and if you had more then 32k in a long raw (as long is not a choice with mod_plsql and uploads) you would have NO WAY to parse it at all in plsql.

a blob would be the only logical datatype to use.

3 did you read the documentation about how file upload works with mod_plsql? you got passed a "key" if you will to a document table where the data actually exists.

check out the docs.

file upload

mo, June 16, 2003 - 12:04 pm UTC

Tom:

1. In this link you say mod_plsql does not support uploading files?

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:3311092584033, <code>

2. which documentation talks about how to do this: is it OTN? Do you mean if I have a text file with "Hello World" i can never get that file into oracle column? it will always be a pointer.

Thank you,

Tom Kyte
June 16, 2003 - 12:16 pm UTC

1) no i didn't, read closer

question was:

...How can I upload a file from client machine to server file system (not
database)
.....

answer is:

file system upload is not supported via mod_plsql.

meaning to upload to a file system (not the database) is not supported. You can upload from your client machine INTO the database.

see
</code> http://docs.oracle.com/docs/cd/A97335_01/index.htm <code>
chapter one, file upload download.

upload

mo, June 16, 2003 - 5:08 pm UTC

Tom:

Thanks,

1. Is there a way to find out the name of the document table?

2. To parse the data you would parse it in the document table or you would copy it to the original table or it does not matter really?

3. When I save the .xls into .csv (text format) some data comes out between double quotes while other does not. Would I still be able to pull the needed info from this using PL/SQL by searching for commas or double quotes and then using SUBTR/INSTR.

Company Code:,,DD11,,,Date:,5/8/03,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
Agency:,,STATE LIBRARY,,,,,
,,"AGENCY, CENTER, EAST",,,,Center,
Address:,,222 LEE PLACE,,,,,
,,"DENVER, CO 22215",,,,,East
,,,,,,,

Thanks,

Tom Kyte
June 17, 2003 - 7:05 am UTC

1) read the documentation please.

</code> http://docs.oracle.com/docs/cd/A97335_01/apps.102/a90099/feature.htm#1006876 <code>

you actually create and name this table if you like.

2) do what is best, easiest and most efficient for you. It is just "code" at this point. Use good algorithms, try different methods, write something efficient and maintainable that performs well.

3) depends on how you write your parsing code! Of course you *can* -- it just makes it a little trickier then simply "search for commas", you have to make sure you are NOT in quotes when you find a comman.

OO4O

Alesinskyy, June 17, 2003 - 7:29 am UTC

Exporting data to CSV and then using it as external table or by SQL*Loader is a good approach, but if it doesn't fit to the specific needs Oracle Objects for OLE may be used. And they come with documentation and samples just for Excel.
And they are downloadable from OTN.

BTW, I usually use them in opposite direction - to make reports in Excel format from data in Oracle.

Strange time format from Excel

Sameer, July 11, 2003 - 11:14 am UTC

Tom,

I am trying to load an Excel sheet data into Oracle. The column which has time, is in HH:MI AM format. But when I pick the value from Excel, I get 0.445138888888889. Is this in some different format that I have to convert while inserting into Oracle ?

I know, this is more like VBA question but I thought there might be Oracle way to convert this number into time.

Thanks in advance !

Ignore my question, found the problem !!!

A reader, July 11, 2003 - 11:19 am UTC


How about Excel 2003 XML interface for import/export

Chowdary, October 28, 2003 - 12:13 pm UTC

Did anyone tried using Excel 2003 XML iterface for importing/manipuating Oracle 9i Data? Read that even it has validation funcionality using smart tags.

Any feedback is welcome.

thanks,
Chowdary

Example Reqd

Srinivas, March 31, 2004 - 6:49 am UTC

Hi Tom,

Thanks, You are doing a great work.

Please can you explain me how to load data from excel sheet into Oracle table with an example.because Iam unable to search in OTN.

Thanks in Advance,
Srinivas


Tom Kyte
March 31, 2004 - 8:51 am UTC

why can't you search in otn? if you can search here, you can search there.

but in any case, I would open the spreadsheet, file save as CSV and use sqlldr.



Script to exract ODBC/XLS/MDB data to flat files

Tak Tang, May 31, 2004 - 5:19 am UTC

Hello folks,

I have put together a script to extract data from ODBC sources, or direct from .xls (MS Excel) or .mdb (MS Access); and dump it out as csv, tab delimited, pipe delimited, or html. I am a unsure of how good it is with Access, because I do not have Access - I have only got a sample database.

You can find it on </code> http://www.orafaq.net/scripts/win/dumpodbc.txt <code>

Please assume that the script CONTAINS A VIRUS, until you have tested it throughly on your test system. Read the comments at the top of the file, which includes the requirements. You will need to save it as 'dumpodbc.js'.

For loading into oracle, I would use TAB or PIPE delimited. Its just so much easier than CSV because of the rules about quoting.

Takmeister


A reader, December 01, 2004 - 4:46 pm UTC


External table pointing to CLOB

VA, February 18, 2005 - 2:17 pm UTC

I used HTMLDB to upload a CSV file into the wwv_flow_files table.

I dont want to write code to parse the csv (terminated by "," optionally enclosed by '"' and all), the external table feature looks perfect for this.

How can I dump the CLOB containing the CSV file into a temp file and use the external table feature to read this temp file? i.e. insert into real_table select * from et;

Thanks

Tom Kyte
February 18, 2005 - 3:05 pm UTC

you would have to use utl_file to write it out.

utl_file

A reader, February 18, 2005 - 3:35 pm UTC

OK I got so far

CREATE OR REPLACE PROCEDURE blob_to_file (
p_id IN NUMBER,
p_filename IN VARCHAR2
)
IS
l_blob BLOB;
l_fp utl_file.file_type;
l_size PLS_INTEGER;
BEGIN

SELECT blob_content INTO l_blob FROM wwv_flow_file_objects$
WHERE id=p_id;

l_size := dbms_lob.getlength(l_blob);
l_fp := utl_file.fopen('upload',p_filename,'w');

EXCEPTION WHEN NO_DATA_FOUND
THEN RAISE_APPLICATION_ERROR(-20000,'No such file with id='||p_id);
END;
/

How can I read the lob and write it to the file?

Thanks

Tom Kyte
February 18, 2005 - 3:46 pm UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_lob.htm#998100 <code>

(or click on the RSS newsfeed tab, it has a link to "code", the code gets XML as a clob and uses htp to print it back, you would use utl_file, but the clob being "written" is sort of the same ;)

Doh!

A reader, February 18, 2005 - 3:49 pm UTC

Doh, you already have a working example at

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:744825627183 <code>

Thanks a lot

What to do under UNIX-compartible environment?

Grigoriy Shpakov, July 01, 2005 - 4:49 am UTC

I have the same problem as discussed here, but with one "little difference" - all the job MUST be done under UNIX-compartible environment. So it's impossible to use such Windows-specific technologies as OLE, ODBC, OO4O, VB(S) and so on. So I need some tool to read .XLS files under UNIX directly into Oracle. Or, at least, some tool to convert .XLS files under UNIX into some temporary format (CSV, for example) and then use some my-own-written software to import temporary file into Oracle. But there is no info here on how to do that.

Tom Kyte
July 01, 2005 - 10:08 am UTC

that is because this is not "askbill@microsoft.com" I suppose? Why would I know all about converting XLS into something under unix?

although point #1 is still valid, the reference to Text.

but you might google around to see if there are any unix utilities to convert an XLS into something more friendly.

You might be able to automate openoffice for example to batch convert them or something.

I need to load excel into oracle by pll in forms ising ole2

A reader, July 25, 2005 - 7:41 am UTC

Hi,Tom,
I have a screen written in Forms6i where the user writes the name of ascii file that is on unix server and when he presses on the button - there is called procedure from package that accepts file name and reads the ascii file by utl_file.get_line. Today the customers whant to load the data direcly from excel file that is on their local computer.
I want to do that like this: when the customer presses on button in the Forms6i screen i want to activate pll from forms which read the data from Excel file into array and after calling pll i want to call the database store procedure that accept the array as parameter and put it into oracle table.I want to read the data from Excel file
with OLE2.I tried to do it but haven't succed.It weel help me if show how to write such pll with ole2 that is activating from Forms 6I.
Thank you very much.
Mari





Tom Kyte
July 25, 2005 - 8:17 am UTC

otn.oracle.com -> discussion forums

sorry but I haven't touched forms in over a decade now...

This forum has technical problem

A reader, July 25, 2005 - 10:14 am UTC

May you refer me to another site that has the examples
off plls from FORM6I that load excel to oracle by ole2?
Thank's a lot,
MARY

Tom Kyte
July 25, 2005 - 11:13 am UTC

otn.oracle.com -> discussion forums

it is up and running from where I sit.

Excel Macro to Export Data to Oracle DB

Christee Carter, August 23, 2005 - 5:04 pm UTC

Here is the Macro that I wrote in Excel to export/append data to an Oracle Table. It starts at the beginning A1 and uploads to the table one line at a time. It may not be the most efficient program as far as loading time, but for my purpose (an average append of 100-300 lines per day) it works fine. Since I already have to run the Macro daily to do Financial Adjustments, it just handles the backup without having to use another tool.

Sub UploadData()

'Set Variables For Login
Dim logon As String
Let logon = "loginName"
Dim password As String
Let password = "Password"

'Set Variables for Upload
'Make sure that your variable have the same data type as
'your Oracle Table
'I had an issue with the Excel Date Format, so I used string
'and then edited it in the SQL statement

Dim Reference As String
Dim LoanNumber As String
Dim Payment As Double
Dim Batch As String
Dim SQLArray As String
Dim AddDate As String
Dim SQLBegin As String

'Loop and retrieve the data that needs to be entered
'into the Oracle DB
Range("A1").Select
Do Until Selection.Offset(0, 1).Value = ""
LoanNumber = Selection.Value
Reference = Selection.Offset(0, 1).Value
Payment = Selection.Offset(0, 2).Value
Batch = Selection.Offset(0, 3).Value
AddDate = Selection.Offset(0, 4).Value

SQLBegin = "INSERT INTO BILL_PAYER_BATCH_HIST (REFERENCE, BATCH, LOAN_NUM, AMOUNT, ADD_DATE) VALUES ("
SQLArray = SQLBegin & "'" & Reference & "','" & Batch & "','" & LoanNumber & "'," & Payment & ", TO_DATE('" & AddDate & "','MMDDYYYY'))"

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=DATAMART;UID=" & logon & ";PWD=" & password & ";DBQ=;DBA=W;APA=T;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;FRL=F;PFC=1000;TL" _
), Array("O=0;")), Destination:=Range("Z1"))
.Sql = Array(SQLArray)
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = False
.BackgroundQuery = False
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SavePassword = False
.SaveData = False
End With
Selection.Offset(1, 0).Select
Loop

End Sub



Best way to load Access onto Oracle

Oracle Learner, October 19, 2005 - 6:19 pm UTC

If you want to load Microsoft Access onto oracle, forget odbc go to

</code> http://www.hxtt.net/index.jsp <code>

and purchase their HXTT ACCESS package.

This allows you to use a real language to load the MS data onto a real database. Because it is a Type IV JDBC driver you can run your java loading routine on a real operating system (like solaris) or loadjava and do it from inside oracle using the embedded jvm

jj, May 01, 2006 - 8:29 pm UTC

how do I post my question in your web site?


Tom Kyte
May 02, 2006 - 3:38 am UTC

well, you just did post one.

When I am taking them (there is one of me), there is a button on the home page - where right now it probably says "sorry, I have a backlog".

Upload Excel into Oracle Table

Christina Carter, July 05, 2006 - 1:06 pm UTC

I created a better procedure than the one before:

Sub AppendOracleTable()

'Create and set login information variables
Dim logon As String
Let logon = "login"
Dim password As String
Let password = "password"

'Set Variables for Upload
Dim Reference As String
Dim LoanNumber As String
Dim Payment As Double
Dim Batch As String
Dim EffDate As Date

'Create and Set Session / Create Dynaset = Column Names
Dim OraSession As Object
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Dim nst As String
nst = "link.odbc.com"
Dim OraDatabase As Object
Set OraDatabase = OraSession.OpenDatabase("" & nst & "", "" & logon & " / " & password & "", 0&)
Dim Oradynaset As Object
Set Oradynaset = OraDatabase.DBCreateDynaset("SELECT * FROM CARTERCH.loan_list", 0&)

Range("A2").Select
Do Until Selection.Value = ""
LoanNumber = Selection.Value
EffDate = Selection.Offset(0, 1).Value
Oradynaset.AddNew
Oradynaset.Fields("LOANNUMBER").Value = LoanNumber
Oradynaset.Fields("EFFDATE").Value = EffDate
Oradynaset.Update
Selection.Offset(1, 0).Select
Loop

End Sub


You can add as many fields as you like to insert many rows of data.

Upload Excel into Oracle Table

Paul Bray, July 06, 2006 - 12:29 am UTC

Thanks for this piece of code. I got it working when I removed "" & nst & "" from the OpenDatabase method and replaced with my ODBC Service Name.

nst = "link.odbc.com"

OraSession.OpenDatabase("" & nst & "", "" & logon & " / " & password & "", 0&)

Cheers,

Paul

excel

sam, August 12, 2006 - 12:38 am UTC

Tom:

Concerning your 1st solution on automating the loading of excel spreadsheet into an oracle table can you elaborate how your proposed solution of using intermedia to convert the spreadsheet into big html and then parse it and load it into an oracle table?

I am trying to create the solution on mod_plsql web application. For example:

a. Create the html form for user.
b. You let the user select the file on his client and then submit it.
c. then what how you invoke intermedia and how do you create the html file and you parse that.

is there an example of how to do this anywhere or can you show us how?


2. How do you handle the spreadsheet if let us say it has header information (like date, po number) and then columns of items, quantities, and prices?

3. Has there been another newer solutions wiht 8i/9i since this was written back in 2001?

thank you,

Tom Kyte
August 12, 2006 - 7:48 pm UTC

step 1; load into the database (a blog)
step 2; using text and the filter functions, convert the binary xls into text html
step 3; parse away (this is the hard part)

writing an html parser in plsql is going to be "non-trivial"


if your server is windows - you could always put the file into the OS after uploading it and use ODBC and the generic connectivity gateway as well
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4406709207206#18830681837358 <code>

excel

sam, August 13, 2006 - 7:25 pm UTC

Tom:

1. It looks the upload of excel into blob column and then converting into html and then parsing the html going to be difficult task.

a. For the parser to work the format of the file has to be always fixed and any changes in colum or row formats will screw up the pl/sql parser. correct?

b. how do you convert a binay xls into html file?

2. The excel file is on user machine on windows xp. The oracle database is on unix. Are you saying the second solution using ODBC would not work (As a user solution)? What I did a few times (developer solution not end user) is to take the spreadsheet and save it to Access and then use ODBC to save that Access file into oracle table. Then you have to parse the data.

3. Since Excel can save a file in comma delimited format is not there a way to upload that file from the user machine into oracle server and then load that into an oracle table? would you be using sqlloader here?

Tom Kyte
August 14, 2006 - 10:39 am UTC

1) yes.

a) depends on how flexible you would code it
b) I set up a spreadsheet with two columns, 4 rows.  row 4 was the sum of rows 2 and 3 (formula) and row 1 was column headings (just used "column 1" and "column 2")

In 10g:

ops$tkyte%ORA10GR2> exec ctx_ddl.create_policy( 'my_policy', 'ctxsys.inso_filter' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> create or replace directory MY_DIR as '/home/tkyte'
  2  /

Directory created.

ops$tkyte%ORA10GR2> declare
  2          l_bfile bfile;
  3          l_clob  clob;
  4  begin
  5          l_bfile := bfilename( 'MY_DIR', 'Book1.xls' );
  6          dbms_lob.fileopen( l_bfile );
  7          ctx_doc.policy_filter( 'my_policy', l_bfile, l_clob, false );
  8          dbms_output.put_line( l_clob );
  9  end;
 10  /
<html><body><p/>
<div dir="ltr">
<table border="1">
<th nowrap width="5"
bgcolor="#ffffff">&nbsp;</th>
<th bgcolor="#ffffff" width="64">A</th>
<th
bgcolor="#ffffff" width="64">B</th>
<tr>
<th bgcolor="#ffffff">1</th>
<td
align="left"><font face="Arial" size="2">
column 1</font></td>
<td
align="left"><font face="Arial" size="2">
column 2</font></td>
</tr>
<tr>
<th
bgcolor="#ffffff">2</th>
<td align="right"><font face="Arial"
size="2">
1</font></td>
<td align="right"><font face="Arial"
size="2">
2</font></td>
</tr>
<tr>
<th bgcolor="#ffffff">3</th>
<td
align="right"><font face="Arial" size="2">
3</font></td>
<td
align="right"><font face="Arial" size="2">
4</font></td>
</tr>
<tr>
<th
bgcolor="#ffffff">4</th>
<td align="right"><font face="Arial"
size="2">
4</font></td>
<td align="right"><font face="Arial"
size="2">
6</font></td>
</tr>
</table>
<p
/>
</div>
<br/><br/><br/></body></html>


PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> declare
  2          l_bfile bfile;
  3          l_clob  clob;
  4  begin
  5          l_bfile := bfilename( 'MY_DIR', 'Book1.xls' );
  6          dbms_lob.fileopen( l_bfile );
  7          ctx_doc.policy_filter( 'my_policy', l_bfile, l_clob, true );
  8          dbms_output.put_line( l_clob );
  9  end;
 10  /

    A  B
 1  column 1  column 2
 2  1  2
 3  3  4
 4  4  6


PL/SQL procedure successfully completed.

prior to 10g, ifilter existed (search this site for that)


2) I don't know what "second solution" you mean - but if you meant from my original answer - you can use ODBC to query the spreadsheet from your own custom application, you can use odbc to write to oracle.

3) sqlldr or any one of hundreds of "do it yourself" techniques, sure. 

excel

Sam, August 15, 2006 - 6:33 pm UTC

Tom:

Excel 2003 can save excel file in XML format. Would it be easier to upload the XML file into a BLOB column and then use the 9i XML parser (instead of HTML)? That should save me writing my own html parser.

2. If yes, do I still need to upload file to database for the oracle parser to run on it?



Tom Kyte
August 15, 2006 - 8:32 pm UTC

csv would be easiest if you just have rows and columns (sqlldr)

if you are going to involve the end user (file save as then upload), use whatever file format works best for you.

If you want to use our xml parsing stuff, yes, you would have to get it to us.

parser

sam, August 19, 2006 - 7:33 am UTC

Tom:

for a user uploading the file, is this an accurate statement.

if I save excel file in HTML it will involve a lot of work writing a parser in pl/sql.

IF it is in XML, oracle has already written the parser and I will save myself a lot of work parsing the xml file and loading it into table.

Tom Kyte
August 19, 2006 - 9:17 am UTC

yes. assuming the xml generated by excel is something usable to you. you might have to write an XSL "program" to reformat into something useful to you.

Move Data From Excel to Oracle Using ADO and OLE DB

Ishaq, August 30, 2006 - 4:32 am UTC

Please Find the Excel VBA to tranfser data from excel to Oracle using ADO and OLE DB

Private Sub CommandButton1_Click()

'create table rab_cash_br_detail_tmp_tab
'(
'account_number varchar2(100),
'reference_no varchar2(100),
'transaction_date varchar2(100),
'description nvarchar2(500),
'cheque_number varchar2(100),
'debit varchar2(100),
'credit varchar2(100),
'balance VARCHAR2(100)
')
'
'create or replace PROCEDURE Rab_Cash_BR_Detail_Tmp (
' account_number_ IN VARCHAR2,
' reference_no_ IN VARCHAR2,
' transaction_date_ IN VARCHAR2,
' description_ IN NVARCHAR2,
' cheque_number_ IN VARCHAR2,
' debit_ IN VARCHAR2,
' credit_ IN VARCHAR2,
' balance_ IN VARCHAR2
') IS
'BEGIN
'
' INSERT INTO rab_cash_br_detail_tmp_tab
' (account_number, reference_no, transaction_date, description,
' cheque_number,
' debit, credit, balance )
' VALUES (account_number_, reference_no_, transaction_date_,
' description_,
' cheque_number_,
' debit_, credit_, balance_ );
'END;
'
'

Range("A5:G5").Select
'Change the connect string as necessary in OraSession.DbOpenDatabase("ExampleDb", "scott/tiger", 0&)


Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command

conn.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=oracle.WORLD;User ID=user;Password=pwd;NDatatype=TRUE;DistribTX=False"
conn.Open

cmd.ActiveConnection = conn
cmd.CommandText = "RAB_CASH_BR_DETAIL_TMP2"
cmd.CommandType = adCmdStoredProc
Set Param1 = cmd.CreateParameter("Prm1", adVarChar, adParamInput, 100)
cmd.Parameters.Append Param1
Set Param2 = cmd.CreateParameter("Prm2", adVarChar, adParamInput, 100)
cmd.Parameters.Append Param2
Set Param3 = cmd.CreateParameter("Prm3", adVarChar, adParamInput, 100)
cmd.Parameters.Append Param3
'Set Param4 = cmd.CreateParameter("Prm4", adVarWStr, adParamInput, 500)
Set Param4 = cmd.CreateParameter("Prm4", adVarWChar, adParamInput, 500)
cmd.Parameters.Append Param4
Set Param5 = cmd.CreateParameter("Prm5", adVarChar, adParamInput, 100)
cmd.Parameters.Append Param5
Set Param6 = cmd.CreateParameter("Prm6", adVarChar, adParamInput, 100)
cmd.Parameters.Append Param6
Set Param7 = cmd.CreateParameter("Prm7", adVarChar, adParamInput, 100)
cmd.Parameters.Append Param7
Set Param8 = cmd.CreateParameter("Prm8", adVarChar, adParamInput, 100)
cmd.Parameters.Append Param8

Dim v_retcode As Integer
Dim Msg, Style, Title, Help, Ctxt, Response, MyString


Msg = "Statement of Account From Excel Sheet will be sent To Oracle " + Chr(13) + " Do you want to continue ?"
' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton1 ' Define buttons.
Title = "Transfer Bank Statement To Oracle" ' Define title.
'Help = "DEMO.HLP" ' Define Help file.
'Ctxt = 1000
' Define topic
' context.
' Display message.
' Response = MsgBox(Msg, Style, Title, Help, Ctxt)
Response = MsgBox(Msg, Style, Title)

If Response = vbYes Then ' User chose Yes.

start_num = 18
num_chars = 18
Acc_No = Mid(Worksheets(1).Cells(5, 1).Value, start_num, num_chars)
MsgBox Acc_No
'MID(Worksheets(1).Cells(5, 1).Value,start_num,num_chars)

Row = 11
Col = 1

MsgBox Worksheets(1).Cells(Row, Col).Value

While Worksheets(1).Cells(Row, Col) <> ""

'Account Number
cmd.Parameters("Prm1").Value = Acc_No

'Reference_No
cmd.Parameters("Prm2").Value = Worksheets(1).Cells(Row, Col + 0).Value

'Transaction_Date_
cmd.Parameters("Prm3").Value = Worksheets(1).Cells(Row, Col + 1).Value

'Description_
cmd.Parameters("Prm4").Value = Worksheets(1).Cells(Row, Col + 2).Value

'Cheque_Number_
cmd.Parameters("Prm5").Value = Worksheets(1).Cells(Row, Col + 3).Value

'Debit_
cmd.Parameters("Prm6").Value = Worksheets(1).Cells(Row, Col + 4).Value

'Credit_
cmd.Parameters("Prm7").Value = Worksheets(1).Cells(Row, Col + 5).Value

'Balance_
cmd.Parameters("Prm8").Value = Worksheets(1).Cells(Row, Col + 6).Value

cmd.Execute

Row = Row + 1
Col = 1
Wend
Else ' User chose No.
MyString = "No" ' Perform some action.
End If

conn.Close
MsgBox "Done"
ActiveWorkbook.Save
Workbooks.Close

End Sub

Also please find the excel sheet for review
Account Report


Report Criteria
Account Number : 123-45678-90-01-23 (SAR)
Date Range : 3/7/2006 to 3/8/2006
Amount Range : Not specified
Cheque Range : Not specified

Reference Date Description Cheque Number Debit Credit Balance
001234 01/04/2006 Sweep To 12345678 -13.83 1,648,887.03
138855 01/04/2006 Cash Deposit &#1576;&#1583;&#1585; &#1575;&#1604;&#1586;&#1575;&#1605;&#1604; 179,100.00 1,827,987.03

Data Loader

Naveed, September 29, 2006 - 11:22 am UTC

I wanted to know the link to the software for loading data from excel sheet to Oracle Application Forms. I used it once and it worked for me but this time i am unable to find the link to download it. To use this software we simply creat Excel sheet and then give reference of excel columns in this software columns and connect the software to data base. It is different from the data loader i found on
</code> http://www.download.com/Data-Loader/3000-10258_4-10453609.html <code>I will be great ful to you if you could help me in this regard.


Tom Kyte
September 30, 2006 - 7:44 am UTC

I don't really know what tool you are looking for, sorry.

Import data from Oracle to Excel

Sridhar.S, November 21, 2006 - 7:09 am UTC

Hi Tom,

This is regarding "importing data from oracle to excel".
I have worked on "Import external data" feature in MS Excel to extract the data from Oracle to Excel(using SQL). But now my requirement is one of the column value in Excel is to be referred in the "WHERE" clause of SQL used in "Import external data".

For e.g.

Input Excel file looks like below:
Emp_Id Emp_Name
1 Tom
2 Sri
3 John
4 Peter
5 Mahe

The required output excel file is like below:
Emp_Id Emp_Name Dept_Id
1 Tom 60
2 Sri 10
3 John 20
4 Peter 50
5 Mahe 30

Here, to get the Dept_Id, I connect to the Department table in the Oracle database through ODBC. The WHERE clause used in the SQL should refer the Emp_Id as reference. Hope I'm clear in explaining my requirement. Please guide me to get a solution for this. Thanks.

Best Regards
Sridhar.S

Tom Kyte
November 22, 2006 - 3:27 pm UTC

would that not be "export data from oracle TO excel"

this should be asked of people that use excel perhaps? Which would not include me in the result set :)

Alexander the ok, February 01, 2007 - 10:38 am UTC

Can we load an excel file using an external table? Can you point somewhere that shows how? I'm wondering if Oracle will recognize the different cells as columns so I don't have to copy into a text file and delimit it myself. I also have to ftp it from my client windows desktop to a unix db server to do this, no idea if that's even possible. Thanks.
Tom Kyte
February 01, 2007 - 1:17 pm UTC

excel is a proprietary binary format, there will be no "recognizing it"

this might help though:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4406709207206#18830681837358

How I eleminate different field from CLOB

A reader, June 11, 2008 - 7:09 am UTC

Tom as per your earlier advice I create the following  codes
SQL> exec ctx_ddl.create_policy( 'my_policy', 'ctxsys.inso_filter' );

SQL> create or replace directory MY_DIR as 'c:\temp'


SQL> 
 declare
           l_bfile bfile;
            l_clob  clob;
    begin
            l_bfile := bfilename( 'MY_DIR', 'Book1.xls' );
            dbms_lob.fileopen( l_bfile );
            ctx_doc.policy_filter( 'my_policy', l_bfile, l_clob, true );
            dbms_output.put_line( l_clob );
   end;
/

OUTPUT is

    A B C  D 

 1  Senthil  2  Debasish  12333 

 2  India 4  Deepak  1100

Is it Possible to store that four columns into a table from that CLOB datatype 

Thankx in Advance

Excel to csv

Dheeraj, August 22, 2008 - 8:38 am UTC

Hi Tom,

Sometime back, you seem to have suggested that xls can be converted to .csv and subsequently loaded by SQLLDR. However, it would fail if the individual xls columns themselves contain , (comma) in the values.

To counter this, I was thinking of converting xls into tab delimited file, which could be consumed by SQLLDR.

Any suggestions shall be highly appreciated.

Thanks,

Dheeraj
Tom Kyte
August 22, 2008 - 2:48 pm UTC

you can, in excel, file "save as" and save as csv.

if there were comma's, it would enclose them in quotes.


try it out.

excel

A reader, March 07, 2009 - 12:49 pm UTC

Tom:

have you written before any articles on those users that like to take data out of DB, put it in a spreadsheet and then sort the data, update it, and then put it back into DB. Their main issue is "simplicity". Application cant do what Excel can.

How do you answer to those users. Do not you lose all kind of data integrity with that. Would you let your customer do it if he asked you to build a program to do it.

Tom Kyte
March 07, 2009 - 12:57 pm UTC

... Application cant do what
Excel can.
...

then, umm, excuse me, but then application was incorrectly coded.

what you just described takes about 30 seconds to develop using APEX. Point at a time, and you can "spreadsheet" it immediately - sort by whatever you want, type over values, delete rows, whatever.

... Do not you lose all kind of data integrity
with that. ....

If data integrity is put where it belongs (in the database), it is impossible to lose it - you cannot by pass it. Only if integrity was (not intelligently) wrapped up entirely in the application would this be true.


spreadsheet

A reader, March 07, 2009 - 1:30 pm UTC

Well let us say we did not have apex for the time being (running oracle server 9.2). we have a web page using pl/sql

also i do not think a WEB HTML page can exactly mimic a spreadsheet kind of look (i.e you can't move columns around, you cant resize the columns, you can't hide columns).
there is more flexibility a spreadsheet can give in viewing the data

it is not easy to code functionality like spreadsheet (i.e sort any column asc, desc, play with the
numbers and see totals, etc.)

What is your definition of Data Integrity? is it Primary Keys, Foreign Keys, column types, column sizes, check constraints


Tom Kyte
March 07, 2009 - 1:39 pm UTC

9.2 has apex.

you can build *anything* you want - really, it is actually true, you can build anything you want.

apex does that....


no no no, let us turn this around, what is YOUR definition of data integrity SMK.

Excel

A reader, March 07, 2009 - 5:47 pm UTC

Tom:

Apex is not supported on 9.2.0.2 it has to be 9.2.0.4,
not only that, they have apex installed on another server. so i have to develop on one server and connect to the other DB on another machine to get data.
i do not want to use DB links for every SQL statement.

I have to wait till 11g upgrade for that.

I installed on my home pC just to see validate your point. you make it sound like you develop a whole application in a few minutes.

Data integrity - to me is ensuring that data is accurate before it gets stored in DB. So this mean validating numbers are numbers and strings are strings, dates are dates, foreign keys have valid values, tables have primrary keys. but that may be only part of it.

Excellent

TAN, November 02, 2009 - 5:29 am UTC

Hi Tom,
Just want to thank you for the wonderful work you are doing. Your suggestons have always proved useful to me. Do keep up the good work.
Thanks..

New way to load spreadsheet data into Oracle

Ivo, September 09, 2011 - 2:51 am UTC

There is another way to import Excel data into Oracle without converting to csv or html first. It uses Linux/Unix and Oracle 11.1.0.7 (or newer). And it is free open source (GPL).

http://www.easydatawarehousing.com/eto-index.php