locked
Importing Data into Sql Server 2012 from Excel Data RRS feed

  • Question

  • Hi,

    I got errors like this when i am doing import data into sql server from excel Data. Can you please help us?

    - Executing (Error)
    Messages
    Error 0xc020901c: Data Flow Task 1: There was an error with Source - demotable$.Outputs[Excel Source Output].Columns[Comment] on Source - demotable$.Outputs[Excel Source Output]. 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 "Source - demotable$.Outputs[Excel Source Output].Columns[Comment]" failed because truncation occurred, and the truncation row disposition on "Source - demotable$.Outputs[Excel Source Output].Columns[Comment]" 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 Source - demotable$ 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)

       
    Wednesday, March 12, 2014 3:02 PM

Answers

  • Are you attempting to import into a newly made table or into an existing table? It looks like it's trying to insert data where it cannot be inserted (invalid column or lack of data size in your column).

    Try the following:

    1). In your excel sheet, highlight the whole sheet and make sure the cells are in 'text' form and try re-importing

    2). save the document as ms dos TEXT and import as a text document.

    3). double check your columns are correct for the data, for example if you have a column that has a string of 100 characters and your column is 'NvarChar(90)' - that might cause the error? Or just correct data type in your column

    3). If that doesn't work and you're inserting into a new table, try importing it as string first and writing a query to insert columns that should be float/integer or whatever. You may want to convert float texts to a 'bigint' first rather than string > float as that can cause problems if I remember correctly.

    • Proposed as answer by Fanny Liu Thursday, March 13, 2014 2:44 AM
    • Marked as answer by Fanny Liu Friday, March 21, 2014 1:47 PM
    Wednesday, March 12, 2014 3:24 PM
  • Looks like data type mapping issue to me. Make sure table fields have sufficient length to accept values coming from Excel source.

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by Fanny Liu Friday, March 28, 2014 2:38 AM
    Wednesday, March 12, 2014 3:36 PM

All replies

  • Are you attempting to import into a newly made table or into an existing table? It looks like it's trying to insert data where it cannot be inserted (invalid column or lack of data size in your column).

    Try the following:

    1). In your excel sheet, highlight the whole sheet and make sure the cells are in 'text' form and try re-importing

    2). save the document as ms dos TEXT and import as a text document.

    3). double check your columns are correct for the data, for example if you have a column that has a string of 100 characters and your column is 'NvarChar(90)' - that might cause the error? Or just correct data type in your column

    3). If that doesn't work and you're inserting into a new table, try importing it as string first and writing a query to insert columns that should be float/integer or whatever. You may want to convert float texts to a 'bigint' first rather than string > float as that can cause problems if I remember correctly.

    • Proposed as answer by Fanny Liu Thursday, March 13, 2014 2:44 AM
    • Marked as answer by Fanny Liu Friday, March 21, 2014 1:47 PM
    Wednesday, March 12, 2014 3:24 PM
  • Looks like data type mapping issue to me. Make sure table fields have sufficient length to accept values coming from Excel source.

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by Fanny Liu Friday, March 28, 2014 2:38 AM
    Wednesday, March 12, 2014 3:36 PM