We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Data Transfer From Excel To Oracle

zubairvp123
zubairvp123 asked
on
Medium Priority
841 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!
      
      

Comment
Watch Question

Author

Commented:
Any Help Available?
CERTIFIED EXPERT
Top Expert 2008

Commented:
Do you use Oracle ODBC?
there are microsoft and Oracle ODBC.
You have to set the Oracle.
Database Administrator
CERTIFIED EXPERT
Commented:
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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

Author

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...
CERTIFIED EXPERT
Top Expert 2008
Commented:
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.

Author

Commented:
Solved!
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
OK, so then are you going to accept one (or more) of the responses here as the asnwer to close the question?
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.