Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Data Transfer From Excel To Oracle

Posted on 2006-11-01
10
Medium Priority
?
820 Views
Last Modified: 2008-01-09
Hi! I'm in the process of getting data from excel to Oracle 8.1.7 using VBA. All my data looks fine and in order but when there are superscript or subscript or other characters in excel then an error occours and the whole operation stops. The error is as follows:
"Run-Time Error-'2147217887 (80040e21)':
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."
Please note that my the data type for this particular field in oracle is Varchar2(50). The data in excel looks like 13 ¾;  or      
 10¾      , etc. All other numbers or fractions or text is not giving any errors. Please help!
      
      

0
Comment
Question by:zubairvp123
  • 4
  • 2
  • 2
10 Comments
 

Author Comment

by:zubairvp123
ID: 17848556
Any Help Available?
0
 
LVL 48

Expert Comment

by:schwertner
ID: 17848652
Do you use Oracle ODBC?
there are microsoft and Oracle ODBC.
You have to set the Oracle.
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 700 total points
ID: 17850116
Yes, maybe using an Oracle ODBC driver instead of a Microsoft (or other) ODBC driver will help.  But if that fails, you may need to use a two-step process:
1. convert the Excel file to a tab-delimitted or comma-delimitted ASCII file
2. use SQL*Loader (or a PL\SQL procedure that uses utl_file) to load the ASCII file into your Oracle table.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:zubairvp123
ID: 17855373
Yes, I do use an oracle ODBC. Thanks.
I tried using the print # initially when writing it to an ASCII file but I'll try write#.
But if there's any other way other than having to use SQL/PLSQL please advise.

Thanks again
0
 

Author Comment

by:zubairvp123
ID: 17855624
Hi markgeer
I tried to write it into an ASCII file with write#1, Range("A1").value but I still get the number and a ? rather than the fraction converted to a decimal. I tried write#1, Range("A1").text, .value2, etc but all give the same result.

Is there any way I can convert the whole 185/8 to a decimal 18.625 in excel itself programatically before having to load it to Oracle?
Need urgent help on this please.
Thanks to all...
0
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 700 total points
ID: 17856556
What about migration to Oracle 9i? It is time to do this - 8i is too old.
In 9i/10g there are so called 'external tables" that could be Excel sheets.

For your question about Excel formating you have to ask guys from the MS Office path.
0
 

Author Comment

by:zubairvp123
ID: 17904027
Solved!
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 17906003
OK, so then are you going to accept one (or more) of the responses here as the asnwer to close the question?
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question