CSV file for null

Question

CSV file for null

MaggieW

SSC Eights!

Points: 947
More actions
December 10, 2014 at 5:28 pm

#293389

Dear all:
I created an application that will upload a csv file and insert it to a SQL table. Some of the columns allow nulls and I would like the null values to show in the SQL table. However the null values didn't show properly in the table.
For example:
id name age salary
1,Mike,null,$100000
2, John,34,$67000
for the first row the age column it'll insert "null" as string
I then tried the following
id name age
3, Luke,,$89000
The inserted value for age is an empty string
I then tried the following
3, Luke,"",$75000
The inserted value for age is double quate
I then tried the following
3, Luke,"Null", $210000
The inserted value is "Null".
So what should I do to make sure a null value can be inserted to the table?
Thank you for the help!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

MaggieW (12/10/2014)
Dear all:
I created an application that will upload a csv file and insert it to a SQL table. Some of the columns allow nulls and I would like the null values to show in the SQL table. However the null values didn't show properly in the table.
For example:
id name age salary
1,Mike,null,$100000
2, John,34,$67000
for the first row the age column it'll insert "null" as string
I then tried the following
id name age
3, Luke,,$89000
The inserted value for age is an empty string
I then tried the following
3, Luke,"",$75000
The inserted value for age is double quate
I then tried the following
3, Luke,"Null", $210000
The inserted value is "Null".
So what should I do to make sure a null value can be inserted to the table?
Thank you for the help!

Don't insert into the final table directly. Always load a staging table first so that you can clean up anomalies like this one and validate data. After you load the staging table, do a simple UPDATE to change the world "NULL" to a real NULL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 2

MaggieW (12/10/2014)
Dear all:
I created an application that will upload a csv file and insert it to a SQL table.

Can you share apllication code? Is it T-Sql, c#, ... ?

MaggieW SSC Eights! Points: 947 More actions · Answer 3

Here's a link you can refer to:

http://www.mssqltips.com/sqlservertip/2556/net-application-for-sql-server-data-management/

MaggieW SSC Eights! Points: 947 More actions · Answer 4

Thank you all for your help.

I decided to use a case when statement in my TSql as follows

insert into Employee (ID, Name, Age, Salary) values (@ID, @Name, (case when @Age = 'NULL' then (Select Null) else @Age end), @Salary)

Thank you again!

Xedni SSCertifiable Points: 6951 More actions · Answer 5

Just FYI there is a TSQL function which obviates the need for your case statement:

declare @age varchar(30) = null

select nullif(@age, 'null')

Executive Junior Cowboy Developer, Esq.[/url]

MaggieW SSC Eights! Points: 947 More actions · Answer 6

Thank you! Jeebee

I learned something new. It's indeed a smart way!

Thank you so much again!