Very Computer

by **Bill Bi** » Thu, 03 Apr 2003 05:21:35

Does anyone know what to do about an Excel Null Problem?

I have a DTS Package that uses an excel file as a data
source. Because I have over 800 fields I list the data
into 3 fields. SheetName, FieldName, FieldData. The
problem is when I import the data The Data Driven Query
Task imports certain fields "FieldData" as Nulls. The
DTS package doesn't seem to like mixed values in the same
column. An Example would be for the first 5 rows;
Test,1,2,3,4. The "Test" field would be imported as a
null because the Data Driven query saw that the rest of
the data is numeric. Is there anything I can do when I
import the data? I tried using "Cast(Fieldname as
Varchar) as [FieldName], but it only gave a syntax error.

I can seperate each of the different data types into
different columns, so that the Data Driven Query only
sees one distinct DataType for each column, but because
they are only 3-D References in Excel, the user can
type.. what ever they want into each cell. (Hence the
problem in a nutshell) The user can mis-arrange data
types in my summary sheet.

Thanks for your help, I would greatly appreciate any
suggestions.

by **JFB** » Thu, 03 Apr 2003 07:19:04

Hi Bill,
Maybe you are in this problem
http://www.sqldts.com/default.aspx?6,222,254,0,1
JFB

Quote:> Does anyone know what to do about an Excel Null Problem?

> I have a DTS Package that uses an excel file as a data
> source. Because I have over 800 fields I list the data
> into 3 fields. SheetName, FieldName, FieldData. The
> problem is when I import the data The Data Driven Query
> Task imports certain fields "FieldData" as Nulls. The
> DTS package doesn't seem to like mixed values in the same
> column. An Example would be for the first 5 rows;
> Test,1,2,3,4. The "Test" field would be imported as a
> null because the Data Driven query saw that the rest of
> the data is numeric. Is there anything I can do when I
> import the data? I tried using "Cast(Fieldname as
> Varchar) as [FieldName], but it only gave a syntax error.

> I can seperate each of the different data types into
> different columns, so that the Data Driven Query only
> sees one distinct DataType for each column, but because
> they are only 3-D References in Excel, the user can
> type.. what ever they want into each cell. (Hence the
> problem in a nutshell) The user can mis-arrange data
> types in my summary sheet.

> Thanks for your help, I would greatly appreciate any
> suggestions.