Export-Excel Powershell Module cannot tranfer all columns - formatting

Problem

The problem is your attempt to extract a *.csv file from the raw data in your first screenshot isn't working properly.

If you start with this:

$lines = @"
Changed database context to 'GPF_PROD_DB'

(0 rows affected)
entry_time batch_run_num batch_name batch_id task_name task_id primary_key chunk_id
---------- ------------- ---------- -------- --------- ------- ----------- --------
       aaa           bbb        ccc      ddd       eee     fff         ggg      hhh

(1 rows affected)
"@ -split "`n"

and your code:

$rawData = $lines | Where-Object {$_ -match '\S'}
$delimiter = [cultureinfo]::CurrentCulture.TextInfo.ListSeparator 
($rawData -replace '\s+' , $delimiter)

you get:

Changed,database,context,to,'GPF_PROD_DB'
(0,rows,affected)
entry_time,batch_run_num,batch_name,batch_id,task_name,task_id,primary_key,chunk_id
----------,-------------,----------,--------,---------,-------,-----------,--------
aaa,bbb,ccc,ddd,eee,fff,ggg,hhh
(1,rows,affected)

and when you read that back in with Import-Csv it takes column headers from the first row - there's only 5 columns so it discards the data in any additional columns (i.e task_id onwards).

Exporting this data to excel (or even back to a CSV with Export-Csv will only write the data from the first 5 columns since that's all that is being imported.

Workaround

You need to extract just the tabular data rows from your source text file.

I don't know how fragile this approach would be, but you could split on the (x rows affected) and take the second result from the text file - e.g.:

# use "Get-Content -Raw" to read the file as a single string in your code
$text = @"
Changed database context to 'GPF_PROD_DB'

(0 rows affected)
entry_time batch_run_num batch_name batch_id task_name task_id primary_key chunk_id
---------- ------------- ---------- -------- --------- ------- ----------- --------
       aaa           bbb        ccc      ddd       eee     fff         ggg      hhh

(1 rows affected)
"@

$rawData = ($text -split "\(\d* rows affected\)")[1].Trim() -split "\n" `
    | foreach-object -begin { $i = 0 } -process { if( $i -ne 1 ) { $_ } $i++ } `
    | foreach-object { $_.Trim() }

What this does is:

• ($text -split "\(\d* rows affected\)") - split the entire text file into chunks based on separators in the format "(x rows affected)"
• [1] - take the second chunk - i.e. the one that contains the tabular data (the array is zero-indexed, so 1 is the second item in the array)
• .Trim() - remove leading and trailing whitespace including line breaks
• -split "\n" - split the chunk into lines
• foreach-object -begin { $i = 0 } -process { if( $i -ne 1 ) { $_ } $i++ } - filters the lines of text in the selected chunk and removes the second one (that contains the ------- column header underlines)
• foreach-object { $_.Trim() } - finally trims the remaining lines of text

The result is equivalent to :

$rawData = @(
    "entry_time batch_run_num batch_name batch_id task_name task_id primary_key chunk_id",
    "aaa           bbb        ccc      ddd       eee     fff         ggg      hhh"
)

and now you can process it like you already were:

$delimiter = [cultureinfo]::CurrentCulture.TextInfo.ListSeparator

# no need to write to a file and re-import - just use ConvertFrom-Csv
$data = ($rawData -replace '\s+', $delimiter) | ConvertFrom-Csv

$data | Export-Excel -Path ...

The result should look like this in Excel:

This might break if your source file format changes, but it'll hopefully point you in the right direction...

Notes

• Updated to discard the line of ------ header underlines in the output from sqlcmd - thanks @mklement0!