Loading data from Excel to Oracle

Hi All,
I would like to load an Excel spreadsheet directly into a single table in 
Oracle. Both the spreadsheet and the table have the same ordering of columns 
and have compatable data.

I am aware that a way to achieve this would be to convert the .xls file into 
a .csv file and then use sql loader. However, I am interested in doing this 
in a quicker/easier way directly from Excel, eg. by adding in some 
functionality and clicking on a new button for loading into the database.

We are all able to read from a database in Excel by importing external data. 
Surely there is a way to change that data and write it back to the database 
from Excel?
kind regards
0
DrSanjay (1)
5/24/2006 3:19:02 PM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
448 Views

Similar Articles

[PageSpeed] 5

Dr. Sanjay,

Following is some code that should help:

Public Function LoadSheetToDB(sSheetName As String, sTableName As String) As 
Boolean
    
    On Error GoTo Err_LoadSheetToDB
    
    Dim rs          As New ADODB.Recordset
    Dim lStartRow   As Long
    Dim lEndRow     As Long
    Dim lStartCol   As Long
    Dim lEndCol     As Long
    
    Dim iRowIndex    As Long
    Dim iColIndex    As Long
    
    sShtName = sSheetName
    
    With Sheets(sSheetName)
        lStartCol = 1
        lEndCol = .Range("Header").Columns.Count
        lStartRow = 1
        lEndRow = .Range("Data").Rows.Count
    End With
    
   
    rs.Open "SELECT * from " & sTableName, GetDBConnection, adOpenStatic, 
adLockOptimistic
    'MsgBox rs("cusip_id")
    
    For iRowIndex = lStartRow To lEndRow
        rs.AddNew
        For iColIndex = lStartCol To lEndCol
            With Sheets(sSheetName)
                Debug.Print .Range("Header").Cells(1, iColIndex).Value
                rs(.Range("Header").Cells(1, iColIndex).Value) = 
..Range("Data").Cells(iRowIndex, iColIndex).Value
            End With
        Next
        
        rs.Update
    Next
    rs.Close
    LoadSheetToDB = True
    
Exit_LoadSheetToDB:
    Set rs = Nothing
    Exit Function
    
Err_LoadSheetToDB:
    LoadSheetToDB = False
    modErrorFunctions.WritetoErrorLogADOErrors "LoadSheetToDB", 
GetDBConnection.Errors, MOD_NAME
    Resume Exit_LoadSheetToDB
End Function

The key elements in this are the .AddNew & .Update methods.  Everything else 
will have to be modified to fit your circumstance.
-- 
http://HelpExcel.com
1-888-INGENIO
1-888-464-3646
x0197758


"Dr Sanjay" wrote:

> Hi All,
> I would like to load an Excel spreadsheet directly into a single table in 
> Oracle. Both the spreadsheet and the table have the same ordering of columns 
> and have compatable data.
> 
> I am aware that a way to achieve this would be to convert the .xls file into 
> a .csv file and then use sql loader. However, I am interested in doing this 
> in a quicker/easier way directly from Excel, eg. by adding in some 
> functionality and clicking on a new button for loading into the database.
> 
> We are all able to read from a database in Excel by importing external data. 
> Surely there is a way to change that data and write it back to the database 
> from Excel?
> kind regards
0
ed5274 (22)
5/26/2006 2:06:02 PM
Reply:

Similar Artilces:

How to I write text onto connectors/lines in Excel?
I want to draw a decision tree flowchart in Excel. Hence I need to write on the connector/line that links two boxes. e.g. I want to write 'YES' and 'NO' onto the connector/line. Is this possible? Thanks Hi, It would appear you can not add text directly to a connection line. You will have to use a textbox. If you need the text and connector to move together you can group the shapes. Cheers Andy Umar wrote: > I want to draw a decision tree flowchart in Excel. > Hence I need to write on the connector/line that links > two boxes. e.g. I want to write 'YES&#...

excel floating digits error
I have created a chart with 2 columns of Nos. and a third column with the results from the subtraction of column 1 from 2. At the bottom of each colum I also have the resultant of the column. the display is to 0 digits but the input on each box is to 2 digits. Also I have selected the numerical to show in Red and parenthesis if it is a negative No. The final resultant at the bottom of column 3 should equal 0. I cannot round to display because I would losse the 2 digit accuracy on the input boxes. The problem that I'm having is that the final resultant shows a negative 0. If I extend...

Extracting data from a worksheet
WinXP, Excel 2000, Novice I have a multi-sheet workbook that I use to track sales and driver earnings for a small courier company (20 drivers). It works well for what I want it to do, but as time goes on my requirements are changing. I don't want to completely redesign my spreadsheet but I need to extract certain data from one or more sheets to create a summary. I actually figured out a way to achieve this from one sheet, but it's not pretty and requires an intermediate sheet whose only purpose is to create the basis for a pivot table. Let me explain. One worksheet is called '...

template with data tracking #2
I created a template using the data tracking wizard. Both the template and database are stored in a network location available to my co-workers. I have no problem creating new files using the template and having the information updated in the database. But it doesn't work for my co-workers. They can create a new file based using the template but when saving the new file there's an error message that says the database file can't be opened. How can I fix this? Dee G ...

Excel and rounding numbers
Hi all, I have a problem that is really driving me batty. I have created an Excel spreadsheet where I keep track of sixteen character credit card numbers. Excel is rounding the last digit up or down...and I can't turn it off. I have gone into "tools/options/calculations" and selected "Precision as displayed", and tried other formatting in the "format, cells" section...nothing is working. Can anyone help me with this frustrating problem? Why is this so difficult!!! Thanks, Mosher Excel has only 15 digits precision that means that everything more than that...

Help with shariing excel file !
Hi, I have strange problem since a few weeks with an excel file I share. This file is stored on aW2003 SBS. It can only be modified by one person (it has a "modify" password is set). The file is *always* opened from the same station, by the same user ! I set this thru : "Save as", then "Tools", "General option". Now, sometimes, when this famous user opens the file, it gets an error, which tells him that... the file is already opened by ... him ! Of course, it can't modify it since the file is exclusive ! I tried to reboot the station, nothing ! The ...

Excel IF Function
I would like some help with a formula. Here's the situation - Cell A5 - dollar amount Cell D5 - either blank or has x in it I would like a formula that says if D5 has an x in it, then change the dollar amount in A5 to a NEGATIVE figure. How do I do that? --- Message posted from http://www.ExcelForum.com/ Given your setup, put this in cell E5 =IF(D5="x",-A5,A5) Be sure that whatever you use for the marker in D5 is identical wit the marker in the " " in the formula, i.e. X in D5, and x in formul will not work properly. It has to be x in D5 and x in the formula. ...

Excel sheet truncated after copying to powerpoint slide #2
Hi, We are using Office 2000 with sp3. When we copy excel sheet to power point slide, some of the rows and colums are truncated. Also the font size is changed. We can see only some part of the sheet. Let me know if anybody faced this problem.. Thanks in advance Shekar. "Shekar" <shekar@mcsb.com> wrote in message news:udouUt8hDHA.1536@TK2MSFTNGP12.phx.gbl... > > Hi, > > We are using Office 2000 with sp3. When we copy excel sheet to power > point slide, some of the rows and colums are truncated. Also the font > size is changed. We can see only some part of ...

Excel 2007 Chart Colors
I posted this a few days ago and no one has responded. Does anyone know how to manipulate the colors of a chart in Excel 2007??? I have the following code that was written in Excel 2003. Every time I run it in Excel 2007 it crashes. I can't find anything about changing the colors of a series in an Excel 2007 chart. Also, when I try to record a macro to change the chart colors to gain insight, nothing gets written to the macro regarding the color change. Help! Sheets("Chart1").SeriesCollection(1).Interior.ColorIndex = 5 You received two replies. It appears...

Version Numbers: ADO vs EXCEL
Some may consider this an ADO item, but it concerns Excel users as well Here's the problem (and a partial solution): An excel worksheet tries to extract and merge tabular data from anothe worksheet located in another workbook(that may be opened or not). Extraction is based on variable criteria. So I used ADO to perform th operation. The Connection string looks like: -Dim strConnection as String strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source= & strExcelWorkbook & ";" & _ "Extended Properties=""Excel 8.0;HDR=...

Excel to delimited txt file in UTF-8 char-set
Dear All, Is Excel capable to save to a delimited text file in UTF- 8 char-set format? I have searched around google group and yahoo, etc... but noone seems to have the answer. It would be really apreciated if someone can help me out of this. Thank you very much in advance. SIM ...

Need help merging selective data
I've got two worksheets, both of them customer lists with company/contact information. One contains the company name and phone number. The other contains company name and address. The lists contain mostly the same companies, but there are variations. The second list has companies not included in the first, and vice versa. The first list is more accurate. Basically, I want the addresses in the second list to become part of the first list, matched up with the appropriate company name, of course. I need to tell Excel to compare the company name fields and import the address when ...

Locking a field for update once data has been entered.
I need to be able to lock a memo field once a user has input data into it so that it cannot be changed. Can someone please provide me with the code i need to include. Thanks. Two situations to consider. A new record or an existing record where no data has been entered in the memo field. Since you did not specify, I will code it so that it is available for entry until the user has made an entry then moved away from the current record. Then, it will not be editable from a form. If your users have direct access to tables, you really can't prevent it. In the Form Current event, te...

Very slow to move around a spreadsheet in Excel 2007
Hi, Just move from Excel XP to 2007, and one particular spreadsheet is very slow after loading it, it opens and closes fine, but once it's opened it works very slow, mving from one cell to another, or by pressing page down or up. I'd checked for formulas, fonts, etc., and I can't find the reason for being so slow. Other spreadsheets work just fine. The original file size was around 50k, the new 2007 version is over 500k! Any ideas? -- Juan Consulting services That's strange. In my experiences, 2007 compresses older .xls files up to 50% and makes the overall file size...

Excel range selection as if dragging, keyboard not responding
I open an Excel file - I click on a cell then nothing - it just selects a range of cells whatever direction I drag the mouse to. Keyboard does not respond, can't exit out unless I do CTRL-ALT-DEL. How do I fix this? Thanks. Sorry, I got it fixed. I had a defective mouse. Duh! Thanks anyway. "lohende" wrote: > I open an Excel file - I click on a cell then nothing - it just selects a > range of cells whatever direction I drag the mouse to. Keyboard does not > respond, can't exit out unless I do CTRL-ALT-DEL. How do I fix this? Thanks. ...

How can I put a tick/check mark in an Excel spreadsheet?
I would like to put a tick symbol in a column in an Excel spreadsheet but cannot find a way to do this. Does any one have any advice? Thanks one way is start accessories system tools character map in characte map change the font to <symbol> and the tick mark is in the sixthrow. you can select and copy to the excel cell. Amanda <Amanda@discussions.microsoft.com> wrote in message news:7442FAC6-DDD6-4B7C-8CFC-CE1FF0D7462A@microsoft.com... > I would like to put a tick symbol in a column in an Excel spreadsheet but > cannot find a way to do this. Does any one have any a...

Excel 2003 Buttons will not appear
i made a file that has buttons to run macros, when i save it on another computer, the buttons disappear. what setting do i need to change to see the buttons? You are posting your question in the wrong forum. This forum is for Dynamics GP. -- Charles Allen, MVP "Josh C" wrote: > i made a file that has buttons to run macros, when i save it on another > computer, the buttons disappear. what setting do i need to change to see the > buttons? Sorry about that, i was in a hurry when i Posted it. "Charles Allen" wrote: > You are posting your question in th...

excel closes when trying to open certain files
Excel 2003 will close when trying to open a specific file. When Excel is reopened the file is in the recovery box. When I open the file from the recovery box it gives a message that the file is too corrupt to repair and most of my data is gone along with any formatting. This is the only file that seems to be affected and it use to open without any problems. The same file will open on other computers with Excel 2003 without any problems. The only difference between this file and others that seem to have no problem is before the file opens, it ask to disable or enable macros. This f...

Display recent documents feature in Excel
How do you turn on the recent documents feature in Excel. The help section says it can be turned on and off, but does not state how to do it. Which version of Excel? -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Tabs man" wrote: > How do you turn on the recent documents feature in Excel. The help section > says it can be turned on and off, but does not state how to do it. ...

PivotChart Data Field
I have a data set of nodes that generated error codes at different times. I want to plot the data with a separate series for each error code, with time on the x-axis and node on the y-axis. I want to use a PivotChart so I can select to show only certain error codes or nodes of interest. I am having trouble setting up the PivotChart (Excel2000). I can't do it as a scatter plot (not allowed for PivotCharts), so I chose Line with Markers. The problem is the data field - the available options are like Sum, Count, Average, etc. but I want it to be Value, as in the value of the node....

How to find the owner of the active excel workbook
Dear Smart People, I'm sure this has been asked before but I'm not able to find the answer. In Visual Basic, how may I find the name or system ID of the owner of the currently active workbook? I apologize if this is an "old" question but I've not been able to gleen the answer. Thank you! Janet Take a look at the BuiltInDocumentProperties to see if you can find what you want there. I'm not sure you can get the "Owner", but you can find who last modified the document. -- HTH, Barb Reinhardt "Janet Panighetti" w...

Can I get excel to sho me the contents of worksheet while scrolling?
And by scrolling I mean dragging from the side scroller. Thanks for the answer =) -Sirritys Nel post news:1151391538.446230.236680@p79g2000cwp.googlegroups.com *Sirritys* ha scritto: > And by scrolling I mean dragging from the side scroller. > > > Thanks for the answer =) > > -Sirritys Hi Sirritys, I'm not sure to have well understood what you mean, but maybe you can try this: select column C, then from menu Window, Freeze Panes. In this way you'll see always column A e B on the screen while horizontal scrolling. -- (I'm not sure of names of menus,...

Excel 05-07-10
How do you center information on the spreadsheet? How do you add a sheet to your document ( I have 3 sheets at bottom but need additional ones)? Hi, select the cells where you want to center and the press the merge center key in your toolbar, has an a in the middle To add a sheet right click on the mouse in the tab name and then insert "PS" wrote: > How do you center information on the spreadsheet? > How do you add a sheet to your document ( I have 3 sheets at bottom but need > additional ones)? Hi, To add a sheet to the workbook press Shift+F11 -- ...

Tally Position of Data
Hello, Sorry for the strange way I have labelled the 'subject' but that is all I could think of. I wish to return the position of a person in a list. That person's position will change daily by the introduction of points gained. There are 3 columns: Column 1 has the persons name Column 2 has the points gained Column 3 is the current position The table is below Col A Col B Col C Name Points Position Tom 40 Dick 50 Harry 25 What is the formula to produce Name Points Position Tom 40 2 Dick 50 1 Harry 25 3 Thanks if yo...

Conditional Formatting: Data Bar, change gradient color based on condition?
I have a worksheet where I'm using single cell conditional formatting based on a range given 2 specific values. So the conditional formatting range is not based on a range of other cells, it's 2 hard-coded values. I'm using a data-bar conditional formatting style. The data-bar serves as a warning indicator if a value falls below a certain number. In my implementation the gradiant displays a yellow bar if the value of a cell is greater than 60% but less than 80%. Over 80% it does not display the data-bar. My question is this: I would like the data-bar to change to RED if the value...