Data Transfer From Excel To Oracle

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!
      
      

zubairvp123Asked:
Who is Participating?
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
zubairvp123Author Commented:
Any Help Available?
0
 
schwertnerCommented:
Do you use Oracle ODBC?
there are microsoft and Oracle ODBC.
You have to set the Oracle.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
zubairvp123Author Commented:
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
 
zubairvp123Author Commented:
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
 
schwertnerCommented:
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
 
zubairvp123Author Commented:
Solved!
0
 
Mark GeerlingsDatabase AdministratorCommented:
OK, so then are you going to accept one (or more) of the responses here as the asnwer to close the question?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.