DTS Excel File Load Error 1

[kristal9]

Hi I am hoping to get some help figuring out an error.

I have an Excel 2003 file and have a DTS built to load it into a SQL 2000 table. Each time it attempts to load, I will get the error msg: DTS Transformation_83’ Error Conversion invalid for datatypes in columns xx to yy.

To enable the file to finally load, each week I must go into the excel file and filter each a select number of columns where the column format is DATE (it doesnt happen with all date datatype columns, just some of them) filter for [blank], select the cells and hit the delete button.

So it seems that SQL is seeing something in those date columns in a cell or series of cells that is not a blank or null value. In excel it appears to be blank value. Even if I try a find " " (space) replace with blank it doesn't find the " " (space). Excel doesn't see a carriage return either but there is something there in what appears to be null value in Excel that is not.

There are in total 6 columns where this occurs. I am wondering if anyone know a way around it ... either in Excel or the DTS to avoid the error without going into the excel file and manually deleting out null values.

I hope this gives enough information. If not, please let me know... also, if this post is better posted in another forum, please advise that as well. I thought to start here.

Many thanks!
kristal

 

[drlex]

kristal,
I have similar problems when importing 'third party' (i.e. other peoples') Excel files into SQL server table. I suspect it is to do with Excel attempting to determine the data types from the initial dozen or so rows.
To address such issues, I tend to use a temporary table with varchar fields as a staging area, so that I can use ISNUMERIC, ISDATE and similar functions to perform checks when loading the data into the final tables.



soi là, soi carré