Search code examples
sql-serverpowershellbulkinsertsql-scriptswindows-scripting

BULK INSERT is replacing BLANK values with NULL

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.

Solution

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

    1. use a NULL

    , OR

    1. 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