Hi,

I am using DTS to import an Excel spreadsheet into a Sql database
table, but I am having trouble with one column. The table does not
allow for any nulls, all data must be imported.

Most of the time the column is alphanumeric, but occasionally it is
numeric. I tried using "Copy Column" for the transformation of all
fields. I have also tried to get it to work by adding VBScript to the
transformation (see below), but it still doesn't recognize the numeric
values which causes the package to fail since null values are not
allowed for the field. I have a different client that has a similar
problem and I require that he put a tick(') in front of the numeric
instances, which works perfectly well. Unfortunately, this type of
manual entry is not an option for this client.

vbscript:
Function Main()
IF IsNumeric( LTRIM(RTRIM(DTSSource("Authorization Number")))) THEN
DTSDestination("ECMDAuthorizationNum") = CSTR(LTRIM(RTRIM(DTSSource("
Authorization Number"))))
ELSE
DTSDestination("ECMDAuthorizationNum") = LTRIM(RTRIM(DTSSource("
Authorization Number")))
END IF
Main = DTSTransformStat_OK
End Function

Example 1 (does not work):
TK727503
TK727503
25071

Example 2 (does work, but requires manual entries):
TK727503
TK727503
'25071

Any suggestions/help would be greatly appreciated.

Thanks in advance,

Amy Bolden

Select the column in excel select Data/Text to columns from the toolbar and
that will convert all the values for you


http://sqlservercode.blogspot.com/



"Amy" wrote:

Hi,

Thanks for the suggestion. Unfortunately this is not a viable solution
since there should not be any manual intervention required by the user
to the spreadsheet (ie. Selecting the column in excel select Data/Text
to columns from the toolbar).

I even changed my process to reading the spreadsheet into a dataset in a
web page and running individual SQL Insert statements. It still blows
up when it gets to a field that it expects text but only has numeric
data. It considers the field to be null.

Any other suggestions would be greatly appreciated!

Thanks,
Amy



*** Sent via Developersdex http://www.developersdex.com ***

Amy,

Allan Mitchell posts a good tip about this. Check this out:

Excel Inserts Null Values
(http://www.sqldts.com/default.aspx?254)


Thomas Bradshaw
Data Integration Specialist
MyWebGrocer.com


--
Message posted via http://www.sqlmonster.com