How to read excel with header and on same time display data without Null ?

Question

i have excel file have column have numbers but it read it as Null value on sql server

i tried that be below

 select * from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;Database=\\192.168.7.9\\Import\6\technologygenerationrulesinsertinput (9)-457ce5cf-33ee-4a46-a556-7854cfcbf27d.xlsx;HDR=No;IMEX=1;','select * FROM [Sheet1$]')  

and it read it success without header of excel when make hdr no

i need to read excel with header and on same time display data without Null

when make hdr no it read success data of column MinFeatureValueNormalized without display null

hdr yes not read data success and still display null for column MinFeatureValueNormalized

to clear what i need when read data from excel using open rowset

it display data below as Null on sql server

so how to handle that please and on same time display column name MinFeatureValueNormalized

my issue solved when make hdr No and imex=1 as above but

header not display so how to solve issue header not display

i attached file have issue with post

207948-datasample.txt

Answer 1

on same time display data without Null

From database view a not entered value is NULL.
If you don't want have NULL (Why, that's DB way?), replace the NULL with any other value using ISNULL function.
https://learn.microsoft.com/en-us/sql/t-sql/functions/isnull-transact-sql?view=sql-server-ver16

Answer 2

Hi @ahmed salah
About IMEX=1, you need to know that Excel has a default setting to guess the data type of a column, which is 8 rows as default. (TypeGuessRows=8 IMEX=1)
You need to change registry value:
1)Close any programs that are running.
On the Start menu, click Run. Type Regedit and click OK.
In the Registry Editor, expand the following key depending on the version of Excel that you are running:
For example: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel Refer to this article for different versions: The logic behind Excel mixed data types

2)Select TypeGuessRows and on the Edit menu click Modify.
In the Edit DWORD Value dialogue box, click Decimal under Base.
Set the value to some big number like 50000, or set it to 0 to force Excel to check all values in the column to choose the data type for the column.
And the same way to set type.ImportMixedTypes: Text

Here is a similar thread that you can refer to: IMEX=1 is not working after certain rows

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.