Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    1

    Question Unanswered: how to get a data from excel?

    hi,
    iam doing a project using oracle,so i wan to get some data from excel using sql,so is there any code to do that

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    You can use any programming language that uses ado/oledb to retrieve the data from excel and import into an oracle database. Oracle might also have an application that allows you to import from various data sources into the database.

  3. #3
    Join Date
    Oct 2003
    Posts
    706

    Lightbulb

    Indeed, and perhaps Excel itself is the best tool to use: "pushing" the data to the Oracle datasource instead of trying to "pull" it from the spreadsheet.

    Within the VBA language, and indeed the Microsoft environment in general, "everything is an object," and is accessible as such.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  4. #4
    Join Date
    Dec 2003
    Location
    Östersund Sweden
    Posts
    60
    Hi guys,

    Yes, I agree with sundialsvcs mainly because it's not an easy task to do it from Oracle (at least not for me!)

    If You not comfortable with VBA in XL then I suggest that You check out the add-in SQL*XL which is available for free (Lite version):

    http://www.oraxcel.com/projects/sqlxl/index.htm

    If You prefer to do the work Yourself then:

    Here You find more information about setting up the connection frm XL to the Oracle-database:

    http://www.able-consulting.com/ADO_Conn.htm

    Below You find a sample on how to create the VBA-code (this is for MySQL):

    Code:
    Option Explicit
    
    Sub Export_Data_ADO__MySQL()
    'You need to set a reference to the MS ADO Library x.x via the Tools | Reference...
    'in the VB-editor.
    
    Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim rnName As Range
    Dim vaFName As Variant, vaEName As Variant
    Dim i As Long
    
    Set wbBook = ThisWorkbook
    Set wsSheet = wbBook.Worksheets("Blad2")
    
    With wsSheet
        Set rnName = .Range("A2:" & .Range("A65536").End(xlUp).Address)
    End With
    
    vaFName = rnName.Offset(0, 1).Value
    vaEName = rnName.Value
    
    Set cnt = New ADODB.Connection
    Set rst = New ADODB.Recordset
    
    cnt.ConnectionString = "DRIVER={MySQL};" _
             & "SERVER=localhost;" _
             & "DATABASE=XLDennis;" _
             & "UID=;PWD=;OPTION=3;"
    
    cnt.Open
    rst.Open "SELECT * FROM tblnamn", cnt, adOpenDynamic, _
             adLockOptimistic
    
    For i = LBound(vaEName) To UBound(vaEName)
        With rst
            .AddNew
            .Fields("FNamn") = vaFName(i, 1)
            .Fields("ENamn") = vaEName(i, 1)
            .Update
        End With
    Next i
    
    rst.Close
    Set rst = Nothing
    cnt.Close
    Set cnt = Nothing
    End Sub
    Kind regards,
    Dennis
    Kind regards,
    Dennis

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •