PowerShell Export-csv writes empty string for a NULL database value. Can it be told to write nothing?

Question

Using PowerShell 4.0 and SQL-Server, I want to merge records from one database to another. I use export-csv and import-csv. I export from one database to a csv file, and import from the csv file to a temporary table in another database, for subsequent MERGE.

TableA is

    ([ID] int not null,
     [Name] varchar(25) not null,
     [StartDate] datetime null,
     [Department] varchar(25) not null)

Values are ID=1, Name=Joe, StartDate=NULL, Department=Sales

exportTable.ps1 (Ignoring database config)

Invoke Sqlcmd ("SELECT FROM TableA WHERE ID=1") | Export-Csv -path a.csv -NoTypeInformation -Append

This results in a.csv

"ID","Name","StartDate","Department"
"1","Joe","","Sales"

import Table.ps1

CreateTable TableATemporary
    ([ID] int not null,
     [Name] varchar(25) not null,
     [StartDate] datetime null)

Import-Csv a.csv | ForEach-Object {
    $allValues = "'"+($_.Psobject.Properties.Value -join "','") + "'"
Invoke Sqlcmd ("INSERT INTO TableATemporary VALUES $allValues")

This gives a table of Values are ID=1, Name=Joe, StartDate=1900-01-01 00:00:00:000, Department=Sales

Rather than a null entry, the datetime field is a default value because the field in the csv file is ""

Is there any way for the Export-Csv cmdlet to write nothing to the csv file for the empty fields in the database, instead of "" ?

Hopefully no one in your database ever has a name like O'Brien or Department like Men's Wear. — Joel Coehoorn
– Joel Coehoorn, Commented Jan 19, 2022 at 19:53

Cpt.Whale · Accepted Answer · 2022-01-19 18:53:53Z

2

Import-Csv always returns blank strings, but there are plenty of ways to set those values to $null if they're empty. For example, here I check for blank values before joining them:

Import-Csv a.csv | ForEach-Object {
    # convert empty strings to null
    $allValues = '"' + (($_.Psobject.Properties.Value | ForEach-Object { 
        if($_){"'$_'"} else{''} }) -join ',') + '"'
    Invoke-Sqlcmd ("INSERT INTO TableATemporary VALUES $allValues")
}

Now it no longer sets empty strings in $allvalues:

"'1','Joe',,'Sales'"

I recommend using Write-SqlTableData for importing rather than running sqlcmd for each row, but it's just an efficiency thing.

answered Jan 19, 2022 at 18:53

Cpt.Whale

5,3911 gold badge15 silver badges17 bronze badges

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

Comments

mklement0 · Accepted Answer · 2022-01-20 20:48:03Z

0

Cpt.Whale's helpful answer shows how to unconditionally represent empty-string field values without embedded quoting ('') [update: should be NULL] in the argument list constructed for the SQL statement.

If you want explicit control over which fields should act this way, you can try the following (simplified example):

[pscustomobject] @{ ID = '1'; Name = 'Joe'; StartDate = ''; Department = '' } | 
  ForEach-Object {
    $_.psobject.Properties.ForEach({
        $quoteChar = "'"
        $name, $value = $_.Name, $_.Value
        # Determine whether to use (unquoted) NULL if the value is the empty string.
        switch ($name) {
          'StartDate' { if ($value -eq '') { $quoteChar = ''; $value = 'NULL' } }
          # Handle other fields here, as needed.
        }
        '{0}{1}{0}' -f $quoteChar, $value, $quoteChar
      }) -join ','
  }

Output (note how the empty-string for StartDate resulted in unquoted NULL, whereas the one for Department is the quoted empty string):

'1','Joe',NULL,''

Note:

Ideally, whether to quote or not should be guided by the data types of the values, but CSV data is by definition all string-typed.
You can implement a data-type mapping of sorts by extending the switch statement above, so that, say, numeric fields are always used unquoted (e.g., as a switch branch for a numeric Age field: 'Age' { $quoteChar = '' }).

edited Jan 20, 2022 at 20:48

answered Jan 19, 2022 at 19:25

mklement0

452k68 gold badges727 silver badges986 bronze badges

4 Comments

mikec Over a year ago

Many thank guys. I was hoping there would be an optional argument to Import-Csv, but I guess not. I was going down the route of dealing with each value explicitly as you are both suggesting. As @mklement0 notes, data types should be inspected so I can distinguish between an empty varChar and null int, datetime etc.

mikec Over a year ago

I tried @Cpt.Whale's solution but of course got an incorrect syntax error on the empty field. Replacing '' with NULL works: Import-Csv a.csv | ForEach-Object { # convert empty strings to null $allValues = '"' + (($_.Psobject.Properties.Value | ForEach-Object { if($_){"'$_'"} else{"NULL"} }) -join ',') + '"' Invoke-Sqlcmd ("INSERT INTO TableATemporary VALUES $allValues") }

mklement0 Over a year ago

Good point, @mikec - I've updated the answer accordingly, which now uses a switch statement that indirectly allows you to implement a data-type mapping by field name. However, if the modified form of Cpt.Whale's solution is sufficient for you, I suggest letting him know, and, once his answer is amended, accepting it.

mikec Over a year ago

After inspecting the 15 tables I am importing, I have used @mklement0's solution which handles a general case as well as specific case. Luckily I have no nullable fields of a varchar data type. A NULL and an empty string both export as an empty string and the exported csv table contains no information about the original value. This is important for validating a round-trip of export and import for a use case of restoring from a backup database.

Collectives™ on Stack Overflow

PowerShell Export-csv writes empty string for a NULL database value. Can it be told to write nothing?

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related