×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

DTS Excel File Load Error

DTS Excel File Load Error

DTS Excel File Load Error

(OP)
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
 

RE: DTS Excel File Load Error

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é

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close