In my script, I am building a custom Powershell object which will be sent to Export-Csv. The receiving party has required that I include some blank columns (no data and no header) and I have no idea how to do that.

If the object looks like this:

$obj = [PSCustomObject][ordered]@{
    EMPLOYER_EIN               = '123456'
    ACTION_CODE                = 1
    LAST_NAME                  = Smith
    FIRST_NAME                 = John
    MIDDLE_INITIAL             = $null
    EMPLOYEE_SSN               = '111-11-1111'
}

How can I have the resulting .csv file's first row look like this:

EMPLOYER_EIN,ACTION_CODE,,LAST_NAME,FIRST_NAME,MIDDLE_INITIAL,,EMPLOYEE_SSN

Put another way, after I run Export-Csv, I want the file to look like this when opened in Excel:

Note the extra columns between action_code/last_name and middle_initial/employee_ssn. I am using PS 5.1 but could use 7 if necessary.

CodePudding user response：

As a test, I created a CSV test.csv with fields A,B, and C, and put a couple of lines of values:

"A","B","C"
1,2,3
4,5,6

I then executed the sequence of commands

Import-CSV -path Test.csv | Select-Object -Prop A," ",B,C | Export-CSV -Path test2.csv

and looked at the resultant test2.csv, which contained

#TYPE Selected.System.Management.Automation.PSCustomObject
"A"," ","B","C"
"1",,"2","3"
"4",,"5","6"

I believe that this is going to be the closest you'll get without manually processing the CSV as a text file.

This is essentially what Santiago Squarzon was suggesting in the comments.

If you need multiple "blank" columns, each one will have to have a header with a different non-zero number of spaces.

CodePudding user response：

I suggest:

• constructing the object with blank dummy properties with a shared name prefix, such as BLANK_, followed by a sequence number (the property names must be unique)

• initially piping to ConvertTo-Csv, which allows use of a -replace operation to replace the dummy property names with empty strings in the first output line (the header line).

• the result - which already is in CSV format - can then be saved to a CSV file with Set-Content.

$obj = [PSCustomObject] @{
  EMPLOYER_EIN               = '123456'
  ACTION_CODE                = 1
  BLANK_1                    = $null    # first dummy property
  LAST_NAME                  = 'Smith'
  FIRST_NAME                 = 'John'
  MIDDLE_INITIAL             = $null
  BLANK_2                    = $null    # second dummy property
  EMPLOYEE_SSN               = '111-11-1111'
}

$first = $true
$obj | 
  ConvertTo-Csv |
  ForEach-Object {
    if ($first) { # header row: replace dummy property names with empty string
      $first = $false
      $_ -replace '\bBLANK_\d '
    }
    else { # data row: pass through
      $_
    }
  } # pipe to Set-Content as needed.

Output (note the blank column names after ACTION CODE and MIDDLE_INITIAL):

"EMPLOYER_EIN","ACTION_CODE","","LAST_NAME","FIRST_NAME","MIDDLE_INITIAL","","EMPLOYEE_SSN"
"123456","1",,"Smith","John",,,"111-11-1111"