Excel and NULLs

[dr00bie]

I have a problem with my DTS package. The way it works is,

A SQL Task reads a table, grabs the ExcelLink column and CardholderID. Set the rowset to a GV.
It loops through and uses the ExcelLink global variable to change the path of the Excel file(source).
It transforms the data to SQL Server, then loops back around, until the rowset is EOF or BOF.

My problem is that I have formatted my Excel file and now the DTS package is importing 918 records, 879 of which are NULL.

How can I build my package to keep from importing NULLs from the Excel file?

Thanks,
Drew

 

[dr00bie]

I think I have found my answer, I will test tommorow and post the answer back here if it works.

Thanks,
Drew

 

[Catadmin]

I have found, when working in Excel, that it is possible to accidently populate rows and columns with blank values, which would end up creating extra rows/columns that would come across in SQL as NULL.

If what you're working on doesn't work, you might try opening the Excel file, then highlighting all rows below the record sets up to (and maybe a little past) the 918th row, then going to Edit -> Delete. It won't look like anything has happened, but it will have cleared out those blank space values.

Then see if you get the same results in your import to SQL.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[dr00bie]

Here is the answer, and it works...

Set the Transform object to use a query instead of just a table, then make your query keep out NULLs like this,

SELECT PONum, PODate, Vendor, Description, Amount, CCCObjectCode,
Carryover, Complete, Credit, PriorCO
FROM [CreditCardLog$]
WHERE (PONum IS NOT NULL)

This will keep from importing the NULLs.

Thanks,
Drew

 

[plevey]

Or change DTS step to use SQL and use WHERE clause to omit rows where <yourcolumn> is NULL