Insert Empty string From Excel To SQL(int)

Question

I have a data in Excel that want to insert into SQL.

There are rows that have a blank, when I try to insert the data into SQL, the data is inserted as '0' instead of 'NULL'.

Example of data as below:

---------------------------------
Items          Total      Price
---------------------------------
Glue             4          $1
Socks            2          $7
Tumbler                     $40
Pen             10          $1
Eraser                      $0.5
---------------------------------

Items(char(50))
Total(int)
Price(float)

Does anyone know how can I insert the blank data as NULL instead of "0" ?

Below is what I have so far.

STRSQL = "INSERT INTO [Stationary].[dbo].[Stocks] ([Items],[Total],[Price])" & _
"VALUES ('" & Range("A" & i) & "','" & Range("B" & i) & "','" & Range("C" & i) & "')"

Really appreciate if anyone can help me on this.

Depending on risk and value of the database, you might also want to sanitise/escape the data you read from the cells to avoid malicious SQL injection. — chillin
– chillin, Commented Aug 28, 2020 at 9:25

FaneDuru · Accepted Answer · 2020-08-28 08:57:52Z

1

Please, try replacing

& Range("B" & i) &

with

& IIf(Range("B" & i).value = "", "'Null'", Range("B" & i).value) &

answered Aug 28, 2020 at 8:57

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ed2 Over a year ago

This should work perfectly. One small enhancement, consider using value2 in place of value, to protect against the (perhaps unlikely) scenario where someone messes with the formatting of that column on you. But, this enhancement is not mandatory.

FaneDuru Over a year ago

@NAJAA BAZILAH: Glad I could help!

Collectives™ on Stack Overflow

Insert Empty string From Excel To SQL(int)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related