BULK INSERT is replacing BLANK values with NULL

Question

I have a huge CSV file with a lot of blank values like:

VALUE1,VALUE2,VALUE3,VALUE4,VALUE5,VALUE6,,VALUE8,VALUE9,VALUE10,,,

That I'm trying to import thru a PS Script. For that I'm using the following powershell script

$Server = 'server_value'
$Database = 'db_value'
$Username = 'usr_value'
$Password = 'pass_value'
$table = 'table_value'
$CSVFilePath = "csv_file_path.csv"
$DbQuery = "BULK INSERT $table FROM '$CSVFilePath' WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR='\n')"

# Create SQL connection
$DbConnection = New-Object System.Data.SqlClient.SqlConnection
$DbConnectionString = "Server = $Server; Database = $Database; User ID=$Username; Password=$password; pooling=false;"
$DbConnection.ConnectionString = $DbConnectionString
$DbConnection.Open()

# Create SQL command
$DbCommand = New-Object System.Data.SQLClient.SQLCommand  
$DbCommand.Connection = $DbConnection 
$DbCommand.CommandText = $DbQuery 

# Execute
$DbCommand.ExecuteNonQuery()

# Close connection
$DbConnection.Close()

The result I'm getting is:

COLUMN01 | COLUMN02 | COLUMN03 | COLUMN04 | COLUMN05 | COLUMN06 | COLUMN07 | COLUMN08 | COLUMN09 | COLUMN10 | COLUMN11 | COLUMN12 | COLUMN13 | COLUMN14
VALUE001 | VALUE002 | VALUE003 | VALUE004 | VALUE005 | VALUE006 |   NULL   | VALUE008 | VALUE009 | VALUE010 | VALUE011 |   NULL   |   NULL   |   NULL

and what I really want is this:

COLUMN01 | COLUMN02 | COLUMN03 | COLUMN04 | COLUMN05 | COLUMN06 | COLUMN07 | COLUMN08 | COLUMN09 | COLUMN10 | COLUMN11 | COLUMN12 | COLUMN13 | COLUMN14
VALUE001 | VALUE002 | VALUE003 | VALUE004 | VALUE005 | VALUE006 |          | VALUE008 | VALUE009 | VALUE010 | VALUE011 |          |          |

I already tried 'single' and "double" quotes on the empty values like this:

VALUE1,VALUE2,VALUE3,VALUE4,VALUE5,VALUE6,'',VALUE8,VALUE9,VALUE10,'','','',''

or:

VALUE1,VALUE2,VALUE3,VALUE4,VALUE5,VALUE6,"",VALUE8,VALUE9,VALUE10,"","","",""

What I got was:

COLUMN01 | COLUMN02 | COLUMN03 | COLUMN04 | COLUMN05 | COLUMN06 | COLUMN07 | COLUMN08 | COLUMN09 | COLUMN10 | COLUMN11 | COLUMN12 | COLUMN13 | COLUMN14
VALUE001 | VALUE002 | VALUE003 | VALUE004 | VALUE005 | VALUE006 |    ''    | VALUE008 | VALUE009 | VALUE010 | VALUE011 |    ''    |    ''    |    ''

or:

COLUMN01 | COLUMN02 | COLUMN03 | COLUMN04 | COLUMN05 | COLUMN06 | COLUMN07 | COLUMN08 | COLUMN09 | COLUMN10 | COLUMN11 | COLUMN12 | COLUMN13 | COLUMN14
VALUE001 | VALUE002 | VALUE003 | VALUE004 | VALUE005 | VALUE006 |    ""    | VALUE008 | VALUE009 | VALUE010 | VALUE011 |    ""    |    ""    |    ""

Also tried with KEEPNULLS on BULK INSERT like this:

BULK INSERT $table
FROM '$CSVFilePath'
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR='\n',
    KEEPNULLS
)

but didn't work.

RThomas · Accepted Answer · 2019-09-11 22:07:33Z

1

When doing bulk insert your options for the way empty values are treated is one of two things.

use a NULL

, OR

use the defined default value for the column

The problem with option 2 is... NULL is default value of the column if you haven't specified anything literal. Meaning without defined default values on your table, your options are NULL OR NULL OR KEEPNULL (which is also NULL).

So..., your only real option here is to alter the table and define an empty string as the default value on all columns where you want this behavior.

See examples here:

https://learn.microsoft.com/en-us/sql/relational-databases/import-export/keep-nulls-or-use-default-values-during-bulk-import-sql-server?view=sql-server-2017

edited Sep 11, 2019 at 22:07

answered Sep 11, 2019 at 21:58

RThomas

10.9k2 gold badges50 silver badges65 bronze badges

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

Collectives™ on Stack Overflow

BULK INSERT is replacing BLANK values with NULL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related