Error converting data type nvarchare to Bit

Question

I'm trying to insert data from excel file with declare values to file. Path is correct for file, file name is correct as declare but throwing following error.

-- Set the Fortuna user importing the file.
declare @ADDUSER varchar(4) = 'DJP'

-- Set the file name you're importing. This file should be stored on the X drive in the folder called Hard Quote Imports.
declare @FileName varchar(150) = 'BOM.xlsx'

-- Set the sheet name containing the item data. If the template is followed, you should not have to change this value.
declare @SheetName varchar(150) = '[BOM Data$]'

DECLARE @free rdp _IMPORT TABLE
(
[ASSM] char NULL,
[ITEM] char NULL,
[ITMDESC] char NULL,
[SEQNO] [int] NULL,
[BEGDATE] [datetime] NULL,
[ENDDATE] [datetime] NULL,
[QTY] [decimal](21, 6) NULL,
[SCRAP] [decimal](21, 6) NULL,
[COST] [decimal](21, 6) NULL,
[PERUNIT] [bit] NULL,
[UNMSID] char NULL,
[CNVF] [decimal](17, 5) NULL,
[REVL] char NULL,
[VARBOM] [bit] NULL,
[FORCECH] [bit] NULL,
[SHRINKAGE] [decimal](9, 2) NULL,
[SPECIAL] [text] NULL,
[ADDUSER] char NULL,
[ADDDATE] [datetime] NULL
)

DECLARE @sSQL nvarchar(max)
SET @sSQL ='SELECT * FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Excel 8.0;Database=E:\XeSQL\Imports\Misc Imports\' + @FileName + ';HDR=YES;IMEX=1'',
''SELECT ASSM, ITEM, LEFT(ITMDESC,35), SEQNO, BEGDATE, ENDDATE, QTY, SCRAP, COST, PERUNIT, UNMSID, CNVF, REVL, VARBOM, [FORCECH], SHRINKAGE, SPECIAL FROM ' + @SheetName +' WHERE ITEM is not null'')'

All above execution coming correct except below statement.

*****print @sSQL
insert into @free rdp _IMPORT (ASSM, ITEM, ITMDESC, SEQNO, BEGDATE, ENDDATE, QTY, SCRAP, COST, PERUNIT, UNMSID, CNVF, REVL, VARBOM, [FORCECH], SHRINKAGE, SPECIAL )
EXEC sp_executesql @sSQL*****

ERROR:-
**SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 8.0;Database=E:\XeSQL\Imports\Misc Imports\BOM.xlsx;HDR=YES;IMEX=1',
'SELECT ASSM, ITEM, LEFT(ITMDESC,35), SEQNO, BEGDATE, ENDDATE, QTY, SCRAP, COST, PERUNIT, UNMSID, CNVF, REVL, VARBOM, [FORCECH], SHRINKAGE, SPECIAL FROM [BOM Data$] WHERE ITEM is not null')
Msg 8114, Level 16, State 1, Line 1
Error converting data type nvarchar to bit.
**

Answer 1

Hi @Jinal Contractor
BIT is an integer data type that can take a value of 1, 0, or NULL.
Only the string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.
SQL server cannot convert string values like 'Yes/No' or 'On/Off' to bit values.
Therefore, you need to check what string values do you have in PERUNIT, VARBOM and FORCECH columns.
For example, assuming your PERUNIT column contains Yes/No values, then you can use a Case When statement to solve it, like this:

SELECT ASSM, ITEM, LEFT(ITMDESC,35), SEQNO, BEGDATE, ENDDATE, QTY, SCRAP, COST  
      ,PERUNIT = CASE PERUNIT WHEN 'Yes' THEN '1' Else '0' END  
      ,UNMSID, CNVF, REVL, VARBOM, [FORCECH], SHRINKAGE, SPECIAL   
FROM [BOM Data$] WHERE ITEM is not null  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

You will be much happier if you always pull data from Excel into a staging table with all varchar fields. Then use a process to convert the values in the staging table into the target table.