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

    Question Unanswered: how to get a data from excel?

    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
    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


    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

  4. #4
    Join Date
    Dec 2003
    Östersund Sweden
    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):

    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:

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

    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;"
    rst.Open "SELECT * FROM tblnamn", cnt, adOpenDynamic, _
    For i = LBound(vaEName) To UBound(vaEName)
        With rst
            .Fields("FNamn") = vaFName(i, 1)
            .Fields("ENamn") = vaEName(i, 1)
        End With
    Next i
    Set rst = Nothing
    Set cnt = Nothing
    End Sub
    Kind regards,
    Kind regards,

Posting Permissions

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