Excel 97 - money is NULL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Excel 97 - money is NULL

Author

Topic

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2006-04-27 : 03:25:10

Hiya

I have almost finished a Package taking a series of worksheets of data and placing these into tables in my DB. Everything works great except for this column:

Fee
----
n/a
n/a
$100.00
n/a
n/a
$50.00
n/a
...etc

My ActiveX transform just sees either "n/a" or NULL. Len() of the column returns 0 for the money amounts. I've checked that the driver matches the version of XL. I've tried IsNumeric, and CCur etc etc. I've also checked that the values in XL are valid (and numeric).

Here's my script code, please tell me what I'm doing wrong...

Function Main()
	Dim c 
	c = DTSSource("Fee").Value
	If c = "n/a" then
		DTSDestination("Dest") = Len(c)
	Else
		If len(c) > 0 then
			DTSDestination("Dest") = c
		Else
			DTSDestination("Dest") = 0
		End if
	End if
	Main = DTSTransformStat_OK
End Function

For the information above, this returns:
Dest
----
3
3
0
3
3
0
3

any ideas?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources