August 28, 2002 at 1:32 pm
I am trying to load an Excel file to a SQL Server table as the follows:
SELECT F3 AS regionid FROM [sim_GDP$] where F3 is not null
And the F3 in the Excle file return text 'NULL', and it can't be inserted into the "int"-type column "regionid" in the SQL Server table "sim_GDP".
What should I do? Is there any conversion function I can use? Where to find functions used in the DTS package?
Thank you.
August 29, 2002 at 9:28 am
Importing from spreadsheets is never an exact science. Go to http://www.sqldts.com and search on "Excel" to see some of the issues. But let's see what we have here.
I put the following into column A of a spreadsheet:
41
Test
44
Null
23
44
Then I created a DTS similar to the one you described:
SELECT F1 AS regionid FROM [sheet1$] where F1 is not null
The result set only contained the numbers, no nulls or text. Imported fine into an int column in SQL Server, with the exception of the 1st number, which DTS takes as a header row (I think there's ways around this, see above link).
OK, let's try again. After reading one of the articles in SqlDTS.com I put the same data into a column that had been formatted for text. Now my query includes the text values which fail to insert into the int column.
So the "easiest" way to resolve this seems to be sure the spreadsheet column is not formatted for text. If you don't have control of that (we rarely do), you can change the tranformation task (you have to change it from the default "copy column" type to an active x) to something like the following:
Function Main()
dim iHold
'trap the error we're going to get when we try to convert text to int
on error resume next
'convert to int; will fail if it's "real" text
iHold = cInt(DTSSource("regionid"))
if err.number = 0 then 'converted ok
DTSDestination("RegionID") = iHold
else ' failed; give it some dummy value
DTSDestination("RegionID") = -9999
end if
Main = DTSTransformStat_OK
End Function
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply