Solved

Data Transfer From Excel To Oracle

Posted on 2006-11-01
10
812 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 47

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 175 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 47

Assisted Solution

by:schwertner
schwertner earned 175 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

808 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