If you have SSIS you could create an import routine to do the same thing as you were attempting in the import wizard, but add in a branch so that any rows that raise an error are written out to a text file (csv, tab-delimited, etc). Remember to also output the row number if this isn't done automatically. This way you can see how many rows are actually causing a problem and decide whether to fix them or ignore them.
Andy Crellin Technical Services Manager Leonard Cheshire Disability Telephone: 01904 479200 E-mail: [email protected] From: Benjamin Zachary - Lists [mailto:[email protected]] Sent: 28 January 2009 15:15 To: NT System Admin Issues Subject: sql importing Just checking if any decent SQL guys out here, I have a bunch of data Ive been asked to import into a new table in SQL 2008, easy enough except the data is pretty messy. I run the import wizard and I keep getting errors about truncation and aborting. I tried to reset the fields to unlimited length of type string or text and still happens. I don't mind truncating the data and went through some advanced options to say ignore truncation but that doesn't seem to work. I brought the data (csv) into excel and saved the xls and then tried to import there but after 6 hours its still not done, so it was probably hung up somewhere. Besides going through 290,000 records of 50 fields by hand is there a decent way to get around that? Im sure for example that my State field shouldn't be 180 chars long on record 10,700 (an error I received) so truncating it to under 50 should be Ok, if I can get around the error. Thanks Internet communications are not secure and therefore Leonard Cheshire Disability does not accept any liability for the content of this message. Any views or opinions presented are solely those of the author and do not necessarily represent those of Leonard Cheshire Disability. If you have received this transmission in error, please contact the sender and delete it immediately. Leonard Cheshire Disability is a company limited by guarantee, registered in England no: 552847, and a registered charity no: 218186 (England & Wales) and no: SC005117 (Scotland) VAT no: 899 3223 75. Registered office: 66 South Lambeth Road, London, SW8 1RL. ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~
