Search code examples
sqlsql-serverexcelopenrowset

SQL JOIN on varchar with special characters and leading zeros

I have a problem with join on varchar column.

I'm going to update SQL data from Excel data by product code but Twr_Kod is varchar and it is the only way to join with Excel data.

There is also problem with convert to varchar when there are leading zeros.

select CDN.Towary.Twr_Kod, excel.Twr_Kod 
from CDN.Towary 
left join openrowset('Microsoft.ACE.OLEDB.12.0', 
                     Excel 8.0;Database=C:\excel\towary.xlsx;',
                     'select * from [Arkusz1$]') excel 
                  on cast(CDN.Towary.Twr_Kod as varchar) = cast(excel.Twr_Kod as varchar)

enter image description here

I know it is not a good solution to join on varchar column but it's necessary so please help why cast is not converting.

Solution

  • The problem is because your excel column contains mixed data types. When importing csv file or excel files with mixed data types column it will replace non dominant types by null. (Using Oledb or Ace.Oledb)

    Workaround

    Add IMEX=1; to your openrowset connectionstring and add a dummy first row containning text values

    Follow my answer at Import error using Openrowset to get more details