Excel Null Problem

Excel Null Problem

Post 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.

 
 
 

Excel Null Problem

Post 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.


 
 
 

1. Export to Excel - problem with NULL

I have a 2 step DTS package which creates a table in an
Excel spreadsheet then populates it from values in a SQL
Server table.

A problem occurs when some columns contain null values.
It is as if the NULL columns from the source data are
ignored and the values from other columns are used instead
so that the data columns in the spreadsheet are shifted to
the left.

The platform is SQL Server 2000 and Excel 2000 running on
Windows 2000.  Latest service packs all round!

Any ideas?

2. Logging on to Personal Oracle through sql*plus?

3. SQL 2000 DTS export to Excel problem with Nulls

4. Data download from web

5. problem setting concat null yields null to false

6. Informix & SSH port forwarding

7. Null values: Classic null value problem (?)

8. DTS from CSV avoiding nulls

9. Concat null yields null problem

10. type problems during union: NULL+NULL produces TEXT

11. Excel to Excel problem

12. Null values reading Excel spreadsheets into ADO recordsets

13. Using ADO to Connect to Excel returns null for numeric types