Importing large text fields in SQL Server 2008 Import Wizard

I am importing a file that has a number of columns including one that has over 800 charachters.

When I use the default settings of nvarchar(255), the import fails - text has been truncated. Fair enough.

I set it the field to nvarchar(4000) and I still get the error - even though I do not have a field that wide.

To top it all - even if I set the mappings to <ignore>, it still complains!!!

Ideally, I would like to get the data in so please answer that for me first.

For future reference, I would like to know how to get round failures on columns I am telling it to ignore..

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rajkumar GsSoftware EngineerCommented:

Check the maximum length of the data using this query like
SELECT MAX(LEN(Column1)) FROM YourTable
SELECT MAX(LEN(Column2)) FROM YourTable

MWilliamsBoltonAuthor Commented:
I already did that in the spreadsheet - adding a column = LEN(Text Column). I then selected checked the max and it was only 355. But for some reason, the import wizard fails if anything is over 255.
Anthony PerkinsCommented:
I am surprised you have not found the solution yet.  It is quite a common problem and has existed since SQL Server 7.  Here is one workaround:
How to import an Excel file which has columns with more than 255 characters to SQL Server using SSIS?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MWilliamsBoltonAuthor Commented:
Thanks for the reply. I will put something in the spreadsheet with 400 chars in it.

In the import wizard, you select which fields to map. Even if I select  for the field mapping, it still fails on that field!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.