How does one change the default varchar 255 of a column when importing data from Excel to Sql Server using Import Export Wizard?

Question

I am trying to import data from excel to Sql Server using Import wizard. Everything seems fine in the first steps. The wizard is able to read the excel file and properly identify columns. But in the final step when the process actually runs, it fails giving the following errors:

Error 0xc020901c: Data Flow Task 1: There was an error with output column "AlternateName" (24) on output "Excel Source Output" (9). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard)

Error 0xc020902a: Data Flow Task 1: The "output column "AlternateName" (24)" failed because truncation occurred, and the truncation row disposition on "output column "AlternateName" (24)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. (SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - Sheet1$" (1) returned error code 0xC020902A. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard)

The column, AlternateName has max data length of 658 chars. The destination table has the column AlternateName defined as varchar(1000). So I was not sure why am I getting this error. But then I noticed this

I have a hunch that this may be causing the issue. But then how do I change the Source column defined as varchar 255 ?

Genesa

Answer 1

You may need to write your own query in the wizard and cast the columns as varchar(1000) - I'm not 100% sure that will work. ALternately, how big is the sheet? The wizard only "looks down" so many rows (IIRC 10000 by default, it's a registry setting) to determine data type and width. Re-sort the excel file so that the longest AlternateName value is at the top.

Nejd

Thanks! The sheet had over 20 thousand rows. I made the first value of AlternateNamevery long and that worked! – Genesa

According to one of the answers here, it only looks at the first 8 rows. – Japan

It only looks at very few rows, definitely not 10000. I have a sheet with less than 100 rows that doesn't import properly. The funny thing is the Flat File Source (instead of Excel) option does have a dialog Advanced > Suggest Types > Number of rows, and you can enter as many rows as needed there. It's strange this is missing when importing Excel. (btw, you can't just convert the Excel sheet to a flat file either - I found the import wizard also had different issues parsing the resulting CSV). – Induration

Answer 2

I had the same problem and I figured out that one workaround is to sort rows which are large and cause problems in Excel document according to cell length from Largest to Smallest because Import Export Wizard only checks first 200 rows.

After rows are resorted Import Export Wizard detects bigger length and change Source column data type to LongText.

Postimpressionism

very nice. Worked for me. I kept the largest value at top and column changed its datatype. Nice trick :) – Arnett

Answer 3

I have the same problem while importing an excel file (version 97-2003) into 2016 SQL server. My Excel file has got 10 columns but 2 of them has large character data ("description" , "output")that are most 600 chars.When I sort the excel based on a column called "description", the truncation error like mentioned above be shown again for another column (called "output") that has also large data in its rows. Even I tried to sort from largest to smallest rows based on "output" column, again the same error be shown to me for "description" column. In addition, when I import that excel into 2008 SQL server, there is no any error after execution. The error is just when I want to import that file into SQL server 2016. I appreciate if anyone solve my problem inorder to import that excel file into 2016 SQL server.

Lalonde

Answer 4

SQLServer just check top 8 rows of excel data by default and estimate the column type and width. So you can change the default setting to top n rows or even remove limitation like this:

Open windows registry with regedit command.

Go to this path in windows registry:

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Jet\4.0\Engines\Excel

Find key with this title:

TypeGuessRows

Double click on it and change the value to what you want. Note that value=0 will force SQLServer to check whole the excel sheet.

Conversable