My DTS package was working great yesterday, so I closed Enterprise Manager
and started working on "prettying" up the excel file. Not anything too
drastic, just validating cells and the like.

I just updated all my Excel files and ran the package. Now the DTS package
is importing 499 records for each Excel file. Each file only has 39 test
records, and there are 3 of these files. So before there was only 117
records imported, now there are 1497 records imported.

I have tried removing all validations from the Excel file, but it keeps
doing it.

Anyone know what is going on?

Thanks,
Drew

Ok... I figured out the problem, I selected all the cells of the Excel file
down to Row 919, then hit Format Cells and hit General. I saved the file
and then tried executing the DTS packge. This time it inserted 918 rows
(keep in mind the first row are column names).

So, how can I keep the Transform from inserting NULL values? Like if the
whole record is NULL, then skip it...

Thanks,
Drew

"Drew" <drew.laing (AT) NOswvtc (DOT) dmhmrsas.virginia.SPMgov> wrote

Hi Drew,

"Drew" wrote:
the Excel provider can behave a little bit strange ;-)
The import continues as long as there are rows with values. But unfortunately sometimes formating is interpreted as data, resulting
in import of NULL values. The only way to avoid this, regardless of formatting, is by specifying a SELECT statement for the excel
data where you define a WHERE clause which column should not be NULL.
Just select the query option for the source of the transformation and use the Tablename shown in table dropdown above.

HTH
Helge

Also

Helge has noted the Excel driver is quirky well what about this.

If the Driver cannot determine the correct data type by sampling rows it
will enter null values for the columns it deems to have broken the
datatype. So in this case your data could be perfectly valid but the
driver will NULL it because it got confused.


Excel Inserts Null Values
(http://www.sqldts.com/default.aspx?254)


Allan


"Helge C. Rutz" <news050424 (AT) nurfuerspam (DOT) de> wrote

Hi Allan and Drew,

"Allan Mitchell" wrote:
ok, lets talk about the second big problem with this jet providers, especially the excel provider.
The article above gives a good understanding of the problem, but there are some more things to say ;-)

Excel uses a variant datatype which allows the change of column datatype row by row, and caused by the auto type guess of excel this
happens all the time. The biggest problem I found is, that if you haven't specified the datatype for a column, you can't change it
afterwards without rekeying all the values that excel stored with the wrong format. The new version show them most time with a small
green triangle - but the work is still the same. So always specify the format before you enter data.

When you use the IMEX Option you must force the provider to identify the right datatype. The TypeGuessRows value can be between 0
and 16, where 0 will force the provider to scan the hole table.
This would be easy if your files are all small. But this option is for all imports on this computer, and an excel import of a very
large file with the rows being scaned twice, because of this option, meens bad performance.
On the other hand, when the provider only find empty fields, it defaults to a text type.
But do you want all unsure types being imported as text?

1. So to avoid all this trouble you should use excel templates with the right formating where ever possible.
2. If you have non text columns with scarce values, you should insert a dummy row at the beginning to force the right type
detection.
3. csv files can also be an alternativ for excel nativ import.

I think thats all for the moment, time for bed now ;-)

Helge

I tried the IMEX=1 trick, to no avail. I will try the suggested query
method tommorow at work. I'll post back for future searchers...

Thanks again for all your help. I searched about my problem, but only
found the IMEX=1 method, and when it didn't work, I thought I would ask...

Thanks,
Drew

Allan Mitchell wrote:

Hi Drew,

"Drew" wrote:
the IMEX=1 trick won't help you here - that's the reason I haven't
described it in the first reply - thats the solution for the second big
problem ;-)
When you have text files, you can easily stop the import at EOF, but with
excel this is differen, because the provider must identify if there are
still more rows with data.
The problem arise from different type formatting in one column, because
excel stores this different type information with the cell, causing the
provider to interpret this cell as not empty.

Try this:
1. open a new excel sheet.
2. insert some text in the first to cells/rows
3. goto cell 100 and format it with a different datatype like text or
number
4. define a transform task with this source and import
Now you should have imported 99 rows where 98 are NULL.

So when you can't control the formatting of the excel sheet you should
expect the worst and avoid this problem with a dedicated select statement..

@Allan: What about adding this information to your excel article?

Helge

Got it working again now, using this statement, Thanks for the help again!

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

Thanks,
Drew

"Helge C. Rutz" <news050424 (AT) nurfuerspam (DOT) de> wrote