Clever Geek Handbook

Excel import to SS2000; Error in the field; DTS

I am trying to import an excel file into a SQL Server 2000 database using DTS. It's nothing out of the ordinary, just a direct import. Where I work, we do this 1000 times a day. This procedure usually works without problems, but something must change in the file.

I am getting the following error:

Error screenshot http://www.understandingguitar.org/wp-content/uploads/2008/12/packageerror-screenshot-20081212.jpg

I have verified that the "AssignmentID" column is stored as "text" in the excel sheet. I also tried changing it to generic. Exact error regardless of setting. The field does contain numbers ... I appreciate everyone's help with this!

Regards,
Frank

0


source to share


3 answers


The answer has to do with the procedure expecting text, but even if you set the properties (in the format dialog) to "text", excel may not treat the data as text. And hence SQL Server (or libraries) won't process it for text.

When routines try to import it, the system thinks it is converting from number to text and expects data to be lost (even if no data is lost) and an error will occur.

If it turns out that I can work around this error by putting "(apostrophe) in front of each number specified. [IE '124321] This force differs in that it treats the number as text.



Hope this saves others the headaches I now have. :-)

Regards,
Frank

0


source


Try to open the excel file and view the column contents.
Is the row value in this column right-justified? (usually for numbers)?



I guess such a line might be the problem.

0


source


It might be obvious, but is the destination string long enough to contain the float string representation? I'm not sure if Excel rounds up what it displays for you, so a wider column is worth trying.

0


source






More articles:


All Articles