Insert blank columns into csv with Powershell

Question

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:

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

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.

Like $headers = $obj | ConvertTo-Csv | Select-Object -First 1 ? — Santiago Squarzon
– Santiago Squarzon, Commented Jan 19, 2023 at 20:13
No, there will be multiple rows in the .csv. This example codes just shows one of them. If I run $obj | Export-Csv C:\temp\test.csv -NoTypeInformation, then I get a normal .csv file. But I need the resulting file to have extra columns. I need a blank column between ACTION_CODE and LAST_NAME and another between MIDDLE_INITIAL and EMPLOYEE_SSN. — StackExchangeGuy
– StackExchangeGuy, Commented Jan 19, 2023 at 20:17
so you're asking to update all rows to fit the new headers basically — Santiago Squarzon
– Santiago Squarzon, Commented Jan 19, 2023 at 20:19
From powershell, your best bet will be to use white space property names i.e. [pscustomobject]@{ ' ' = $null } — Santiago Squarzon
– Santiago Squarzon, Commented Jan 19, 2023 at 20:23
Is it acceptable if the header for the "blank" column is a single space? — Jeff Zeitlin
– Jeff Zeitlin, Commented Jan 19, 2023 at 20:23

Jeff Zeitlin · Accepted Answer · 2023-01-19 20:27:42Z

2

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.

answered Jan 19, 2023 at 20:27

Jeff Zeitlin

11k2 gold badges26 silver badges38 bronze badges

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

3 Comments

Jeff Zeitlin Over a year ago

Obviously, I forgot the -NoTypeInformation for the Export-CSV, but it's still good enough to illustrate the technique...

StackExchangeGuy Over a year ago

That only works for one "filler" column. When I add the second one, PS says, "Select-Object : The property cannot be processed because the property " " already exists.". I will check with the recipient if I can have each "filler" column contain one more blank space than the one before it.

Jeff Zeitlin Over a year ago

@StackExchangeGuy - Use " " for the first null column, " " for the second, " " for the third, et cetera.

mklement0 · Accepted Answer · 2023-01-19 20:58:39Z

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"

Collectives™ on Stack Overflow

Insert blank columns into csv with Powershell

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related