SQL import from excel source not pulling function result through

Posted on 2007-08-08
Last Modified: 2013-11-30
I have created an import in SQL 2005 to pull data from a workbook in Excel.  One of the fields I need to pull into the table is the result of a function.  When I run the import job everything comes across fine, except for the function result.  These fields are coming across as zeros.  Is it possible to import the result of a function?

Thanks in advance,
Question by:breault
    LVL 23

    Accepted Solution

    Hi breault,

    Unfortunatly SQL will not grab UDF's from excel as they are not defined in SQL. What you could do is run a quick macro in Excel which will copy the data to a values only sheet, which you can then import. Code to do this would be:

    Sub CopySheetToValues()
    Dim SourceSheet As Worksheet
    Dim DestSheet As Worksheet

    Set SourceSheet = Sheets("myDataWithFormulae")
    On Error Resume Next
    Set DestSheet = Sheets("myDataValues")
    On Error GoTo 0
    If DestSheet Is Nothing Then
    Set DestSheet = Sheets.Add
    DestSheet.Name = "myDataValues"
    End If


    DestSheet.Cells.PasteSpecial Paste:=xlValues
    DestSheet.Cells.PasteSpecial Paste:=xlFormats
    Application.CutCopyMode = False

    Set SourceSheet = Nothing: Set DestSheet = Nothing

    End Sub


    Author Comment

    Nice...Thanks MalicUK.  Your solution worked well...
    LVL 23

    Expert Comment

    No problem!

    I notice you are new to EE. To get info on closing out questions please see:

    LVL 23

    Expert Comment

    Thanks for the grade! :)

    Author Comment

    thx for the quick assist...

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now