SQL Error while importing Data From Excel

Question

I am importing Data from excel sheet. I am struggling with the following problems -

Executing (Error) Messages Error 0xc020901c: Data Flow Task 1: There was an error with output column "Intelligence" (21) 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 "Intelligence" (21)" failed because truncation occurred, and the truncation row disposition on "output column "Intelligence" (21)" 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 - MainSheetData$" (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)

score 6 · Answer 1 · answered Jan 05 '14 at 14:24

6

I was banging my head against the wall with this same exact error.

Try importing into MS Access and then importing into SQL Server.

answered Jan 05 '14 at 14:24

Kevin

895
2
10
21

score 6 · Answer 2 · answered Jan 01 '18 at 23:12

6

turns out it only checks first 8 rows or so of the Excel sheet..so if it decides length is 225 and later on encounters more than 225 chars an error occurs , what I did to solve the problem was make a first fake row containing the worst scenario (max of everything) and problem solved !

answered Jan 01 '18 at 23:12

Hind

333
3
7

It worked for me too. Moved a row that was bigger, to the top, and then it worked. In import settings, I told that the problem column was a nvarchar(max), but I still had the error. Thanks! – danicode May 21 '19 at 07:30
It worked for me too. Moved a row that was bigger, to the top, and then it worked. In import settings, I told that the problem column was a nvarchar(max), but I still had the error. Thanks! – danicode May 21 '19 at 07:30

score 1 · Answer 3 · answered Dec 13 '12 at 13:12

1

The first error is telling you that your source data for the Intelligence column is either longer than your target column or contains charachers that your target column cannot accept.

The second error is telling you that the Intelligence column is longer than your target column and therefore its failing. I expect this is the true issue.

You can either

expand the size of your target column to cover the larger input or
switch the Error Output of the component to "Ignore failure" on Truncation

answered Dec 13 '12 at 13:12

Daryl Wenman-Bateson

3,870
1
20
37

i have changed it to nvarchar(max) but still the error is the same. – Kumar Dec 13 '12 at 13:17
Which component is failing? Is it your source, target or an intermediary component? If its source, you will have to amend the definition of the output columns within the source not your target database – Daryl Wenman-Bateson Dec 13 '12 at 13:54
Error is in the "Intelligence Column". In my view it is in excel sheet means source. Then how to amend the definition of o/p columns. In excel; in the perticuler column there are some text lines. I increased the size by nvarchar(max); but still getting the same error. – Kumar Dec 14 '12 at 09:44
Are you using ssis or sql import / export wizard? If ssis, you can drill down into the error to see if its reading the source or writing to target that has the issue in debug (failed components turn red) and amend the properties (right click -> show advenced editor) accordingly. If its SQL, use the Edit mappings to increase source column size. Try changing it to nvarchar to cover extended character set, and Create New Destination table – Daryl Wenman-Bateson Dec 14 '12 at 10:43

score 1 · Answer 4 · edited May 23 '17 at 11:46

I was having the very same issue, and although I tried numerous suggestions from searching here, the option that worked for me was to convert the excel file to a CSV and use a bulk insert command instead.

This bypassed the need to edit mappings which wasn't working for me. I had a field that was not updating when I changed the field type.

Code below from this answer:

BULK INSERT TableName
        FROM 'C:\SomeDirectory\my table.txt'
            WITH
    (
                FIELDTERMINATOR = '\t',
                ROWTERMINATOR = '\n'
    )
GO

score 0 · Answer 5 · answered Jan 05 '14 at 13:21

Importing using CSV is difficult as the import process doesn't know the max length for any field. Therefore when it hits a row longer than the initial column length it errors.

Simply save your csv file as a excel workbook and re import. You'll need to delete an existing tables that were created before failute last time.

As it's excel, it can obtain the correct field length when creating the table.

score 0 · Answer 6 · answered Apr 15 '14 at 12:31

0

I was getting the same error while importing from Excel to SQL Server 2008. I was able to do it by exporting from xlsx to csv and then importing the csv file into Sql Server. Yes, I had to adjust the columns length by hand but it worked just fine!

answered Apr 15 '14 at 12:31

knifebright

1

score 0 · Answer 7 · answered Feb 25 '15 at 20:28

I was having the same problem and had to manually go through Excel to find the problem. One time saver, if you click Report -> View Report at the bottom, it will open up a new window. If you scroll all the way to the bottom of the report, it will tell you how many rows were processed. It doesn't necessarily mean that the problem is in the next row, but at least you can skip going through all the rows before that.

What I did next in Excel was take only the amount of characters that would fit into SQL (i.e. LEFT([Column], 255) and truncate the rest.

It is not ideal, but it worked in my case.

score 0 · Answer 8 · answered Dec 15 '15 at 09:46

0

Export

You need to change "On Error" option to Ignore and "On Truncation" option to Ignore in Review Data Type Mapping. This will solve the problem.

answered Dec 15 '15 at 09:46

devi

1

score 0 · Answer 9 · answered Mar 01 '17 at 11:34

I am not sure, if anyone has tried this or not:

Copy the content of the file from excel .xls or whatever excel format it is in currently and paste it into new excel file as value. Save the file in .xlsx format and try importing again with sql server.

It will be a success!!

score -1 · Answer 10 · answered May 06 '20 at 10:42

-1

It is enough to place the biggest length in the first row. Then it functions.

answered May 06 '20 at 10:42

Hasbi Sevim

1
1

SQL Error while importing Data From Excel

10 Answers10

Linked