Wednesday, August 26, 2009

MS Excel friendlier to MS Access than to SQL 2005

Ever come across this error while trying to import data into SQL Server 2005 from Excel?
"Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard)
I came across this and googled with no luck. I realized I had faced this same situation some time earlier and my rescue was MS Access. It seems MS Excel is more friendlier to MS Access while MS SQL is less forgiving. If you run across this issue the simplest solution is to import the data in MS Access and then Import the data to MS SQL from MS Access. I agree this is an extra step but its better than spending time fixing the Excel import in MS SQL when you really don't want to be spending time fixing something which you may not want to use quite often.

Having said the above, there may be better ways of resolving this. If you know of any pls do leave a comment with the solution or link.

Happy Programming...