August 28, 2002 at 4:35 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 28, 2002 at 5:08 pm
IS it coming thru as NULL or '' (blank), if later try adding AND F3 != ''. I also believe this was asked before in the groups, you might want to search.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
August 29, 2002 at 1:35 am
In my Excel file, the value in F3 are integers, otherwise, it shows 'NULL' text. I want the DTS transmation will insert value NULL to the "regionid" column if the Excel cell value is 'NULL' text, insert integer value to the "regionid" if the Excel value is integer.
Is there any function (called it X) I can use to do this, like what I show below?
SELECT F1 As title, X(F3, NULL) AS regionid FROM [sim_GDP$] where F3 is not null
because when F3 is 'NULL', I still need to insert a record to the sim_GDP table, with F3 = NULL and other columns have values. So I can't add 'AND F3 != '' in the SQL.
Have been looking for the previous post, don't see similar problem.
August 29, 2002 at 3:54 am
We also had some problems importing Excel files some time ago.
Don't remember the exact stuff, but it boiled down to a problem with Excel 'guessing' the type of a column from the first X lines. And if he guesses wrong, it's a string and not an integer of course. So 'NULL' will be recognized as string "NULL" and not as the null value.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply