How to handle Numeric or Date Null Data or COlumn when creating query to insert into excel sheet

I am creating Excel Sheet using Microsoft.Jet.OLEDB.4.0 Create Table Query. It works well and I can transfer my data to Excel Sheet.

The problem is whenever I have some numeric or date data and it is null then it passes nothing in query so there is error of Insert Into.

Whenever I have stateid is null then it forms query like Insert Into City (CityID,City,StateID) Values (1,'Mumbai',).

How to handle Numeric or Date Null Data or COlumn when creating query to insert into excel sheet?

Excel is not true structured database. In a case if columns inside of the spreadsheet contain mixed data, provider will start to return and store NULL values if it cannot detect type of the cell. For example, assuming spreadsheet has column A1 where some values are strings and other one dates. First provider scans first N rows and detects datatype of the column. If it detects that column contains strings, then it will work with the dates as with NULLs and vice versa. The only thing you could do with Jet 4.0 is to force it to treat all the values as strings, if you add IMEX=1 to the Extended Properties of the connection string. But in this case you will lose data types completely. I faced this issue long time ago and I decided to go my own way of creating component for it. Another way is to use Office Tools from Microsoft, but they still COM based and require a lot of the resources during run-time