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.

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:

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

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

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:

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:

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.

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:

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?

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.

<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 ?!

Ha ha that was a good answer Tom. ;)

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.

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.

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:

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:

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:

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,

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.

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 !

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

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

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

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

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

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

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.

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

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

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

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

how do I post my question in your web site?

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.

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

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:

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?

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. 

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:

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.

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

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.

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

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 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

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:

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.

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:

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.

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..

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