Forum Discussion

Tim Hunter's avatar
Tim Hunter
Steel Contributor
Jan 16, 2019

Importing Data from Excel to SQL table

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 th...
Tim Hunter's avatar
Tim Hunter
Steel Contributor
Jan 18, 2019

Christian_McGhee

 

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

Pio-Balistoy's avatar
Pio-Balistoy
Copper Contributor
Jan 24, 2019
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.
  • Tim Hunter's avatar
    Tim Hunter
    Steel Contributor
    Jan 24, 2019

    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?

    • Christian_McGhee's avatar
      Christian_McGhee
      MCT
      Jan 24, 2019

       

      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

      • Tim Hunter's avatar
        Tim Hunter
        Steel Contributor
        Jan 29, 2019

        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!

Resources