• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 824
  • Last Modified:

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!
      
      

0
zubairvp123
Asked:
zubairvp123
  • 4
  • 2
  • 2
2 Solutions
 
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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now