[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • Last Modified:

SQL import from excel source not pulling function result through

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,
B
0
breault
Asked:
breault
  • 3
  • 2
1 Solution
 
MalicUKCommented:
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.Clear

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

Set SourceSheet = Nothing: Set DestSheet = Nothing

End Sub

Cheers,
MalicUK.
0
 
breaultAuthor Commented:
Nice...Thanks MalicUK.  Your solution worked well...
0
 
MalicUKCommented:
No problem!

I notice you are new to EE. To get info on closing out questions please see:
http://www.experts-exchange.com/help.jsp#hs5

MUK.
0
 
MalicUKCommented:
Thanks for the grade! :)
0
 
breaultAuthor Commented:
thx for the quick assist...
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now