CODEWITHSUNDEEP
sql-server-2008excelssis
Tyler Mortensen
Tyler Mortensen

Reputation: 461

Importing Excel to Sql Causing Truncation Failure

I am trying to add about 350 rows of data from an Excel sheet into SQL Server 2008 using the Import and Export Wizard. I am running into a single issue that I cannot find a solution for. I have a column named Link with a text data type in my SQL table to hold URLs (since they can get pretty long sometimes). I have a corresponding Link column in my Excel sheet whose longest entry is exactly 100 characters. When I run the Import/Export Wizard, I receive a series of errors related to the truncation, the first of which states "Data conversion failed while converting column "Link" (60) to column "Link" (168)."

After extensive Google-ing, I have been unable to find a solution. The first suggestion everyone makes is to set the longest field as the first row in your Excel sheet, that way SQL will know how long to expect the field to get. I have done this, to no avail. Does anyone have any other suggestions?

I just don't understand how a SQL column with a data type of text (with a max length of just over a billion characters) would need to truncate a 100 character long cell.

Upvotes: 0

Views: 4042

Answers (1)

Tyler Mortensen
Tyler Mortensen

Reputation: 461

I have found a fix for my problem. First, I let the Import Wizard create a new table from the Excel sheet to see which data type it picked for the Link column. It chose nvarchar(255), so I went into my SQL table and changed the data type from text to nvarchar(255). It imported everything on the first try with no problems. I am not sure why it would not input the data into a text column (I have several other text columns that worked just fine). It may have been something to do with the slashes, colons, ampersands, etc. that exist in a URL, but for whatever reason, it would not put the data into a text data type. Oh well. C'est la vie.

Upvotes: 1

Related Questions