Importing Data from Excel to SQL table

[Tim Hunter]

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)

[#2]

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

[#3]

@Christian_McGhee

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

[#4]

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.

[#5]

@Pio Balistoy  @Christian_McGhee 

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?

[#6]

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>Export>Change File Type>Save As. 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 .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 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. Let me know if this works

@tim @Pio

[#7]

@Christian_McGhee   @Pio Balistoy 

I tried your suggestion, saving as CSV and importing as a Flat File but still getting errors:

Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "tech_comment" returned status value 4 and status text "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 - helpdesk2019_csv.Outputs[Flat File Source Output].Columns[tech_comment]" failed because truncation occurred, and the truncation row disposition on "Source - helpdesk2019_csv.Outputs[Flat File Source Output].Columns[tech_comment]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "\\thunter-laptop\users\thunter\Desktop\helpdesk2019.csv" on data row 2.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source - helpdesk2019_csv returned error code 0xC0202092. 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)

[#8]

Any other ideas on how to get this data imported. I have not found any other solutions via Googling? Thank you.

[#9]

@Christian_McGhee @Pio Balistoy 

After doing a little further testing, I figured out that the SQL table column did not like the ' character in my Excel column. Is there an SQL Data Type that will accept the ' so I don't have to remove them all from Excel? Thank you!