0

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.

Tyler Mortensen
  • 451
  • 3
  • 8
  • 19
  • Who says `Link` is `text` data type? SQL server or Excel? You should only trust SQL server. – Davor Josipovic Jun 18 '13 at 18:38
  • The problem is that the wizard is going to base sizing on the source (Excel) and not the destination (SQL Server). SSIS is fine stuffing something small into something much larger. By default, Excel is going to examine the first 8 rows to try and guess what the underlying data looks like. There's a setting you can tweak to indicate that the Excel driver should look further into the file. It's not IMEX but if you take a quick search through SO, it should come up with the setting as people often have to set the imex value with this other property – billinkc Jun 18 '13 at 18:46
  • I've just worked on something small with SSIS, and I opted to open the Excel import files, save as Tab Delimited Text, and imported that via SSIS. It was less of a headache. SSIS read the Excel metadata incorrectly, or Excel reported its metadata to SSIS incorrectly, for many columns that I had. – HardCode Jun 18 '13 at 18:49
  • Davor, SQL has the Link column as Data Type text. billinkc, I am not sure what most of the things you said mean. What are IMEX and SO? HardCode, after you commented, I tried saving the Excel sheet as a tab delimited text file and importing it. I received the same error, except this time it was referred to as Link (70) instead of Link (60). – Tyler Mortensen Jun 18 '13 at 19:12

1 Answers1

1

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.

Tyler Mortensen
  • 451
  • 3
  • 8
  • 19