Importing Data from Excel to SQL table

Question

I am getting truncation errors while importing Excel spreadsheet to SQL table. Not quite sure how to fix this. My column in SQL table is plenty long enough to hold the contents of column. Here are the errors:

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

christian_mcghee · Answer

&nbsp;It would seem that you have one or more characters that are not being recognized by the code page you are using.&nbsp;Change data type for destination column to nvarchar(xx) Unicode. That should do it I suspect.&nbsp;

pio-balistoy · Answer

AS Christian pointed out, it may not be an issue on the data type but on the collation. There is a character on the excel that is not recognized by the Collation of the database/Instance. You can check the Description column on the Ticket worksheet. You can workaround this by changing the column/table/database collation for the destination. Or re save/import the excel file to a compatible encoding.

christian_mcghee · Answer

&nbsp;Hmm,Rather than trying to troubleshoot you Collation problem lets see if we can fix this another way. Try Saving the excel file to a .csv. From Excel: File&gt;Export&gt;Change File Type&gt;Save&nbsp;As.&nbsp;Then using the "SQL server Import Export Wizard" select as the "Source" a "Flat File Source" from the drop down list and browse to the location of your saved&nbsp;.csv. Make sure when browsing you tell it to look for the .csv file extension because it defaults to .txt. Then as your destination select "SQL Server Native Client 11.0" from the drop down list. After clicking Next you&nbsp;should be able to edit the mappings and destination data types as desired. Other wise its a bit hard to trouble shoot without hands on access.&nbsp;Let me know if this works&nbsp;tim @Pio

tim hunter · Answer

Christian_McGhee&nbsp;I do have all my columns set to nvarchar and still getting the truncation error. Any other ideas?

tim hunter · Answer

Pio-Balistoy&nbsp;&nbsp;Christian_McGhee&nbsp;&nbsp;Thanks again for your feedback and help! I have tried the Description column in my Excel as both Text and General but still does not work. My description column in SQL table is nvarchar(1500), should I be using a different Data Type on the column in SQL? Or is there a better way to save my Excel spreadsheet before importing?

Forum Discussion

Importing Data from Excel to SQL table

8 Replies

Resources