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

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 "" ?

CodePudding user response：

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.

CodePudding user response：

Cpt.Whale's helpful answer shows how to unconditionally represent empty-string field values without embedded quoting ('') in the argument list constructed for the SQL statement, so as to treat them as not specified.

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({
      # Determine whether to quote the field value at hand.
      # Specify the names of one ore more fields (,-separated) as the RHS of -in, 
      # for which empty values should be left as-is (no quoting).
      $quoteChar = ("'", '')[$_.Name -in 'StartDate' -and '' -eq $_.Value]
      '{0}{1}{0}' -f $quoteChar, $_.Value, $quoteChar
      }) -join ','
  }

Output (note how the empty value for StartDate is unquoted, whereas the one for Department is quoted):

'1','Joe',,''

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.

Alternatively, the decision to quote / how to format the values could be based on a hashtable (dictionary) that maps the CSV column (field) names to their ultimate target data types.