Working with excel

PowerShell Help
1

Hello,

I am trying to output text from two different variables into two different columns in an excel sheet.

The two variables are $Default and $custom which contain list of values.

 

=====================================================

# Table name
$tabName = “files”

# Create Table object
$table = New-Object system.Data.DataTable “$tableName”

# Define Columns
$col1 = New-Object system.Data.DataColumn "Defaultfiles",([string])

# Add the Columns
$table.columns.add($col1)

$col2 = New-Object system.Data.DataColumn "Customfiles",([string])

$table.columns.add($col2)

Foreach ($line in $Default) {
# Create a row
$row1 = $table.NewRow()
$row1."Defaultfiles" = $line
$table.rows.Add($row1)
}

Foreach ($value in $custom) {

# Create a row
$row2 = $table.NewRow()
$row2."Customfiles" = $value
$table.rows.Add($row2)

}

$table | Export-Csv -Path C:\Temp\files.csv -NoTypeInformation

 

===========================================================

Desired output:

Defaultfiles Customfiles
oof.json AP.json
analytics.json OOPS.json
all-performance.json opp1.json
memory-2.json opp2.json
windows-server-2016.json Performance.json
generic.json Windows Server 2016.json
memory.json
Current Output:
Defaultfiles Customfiles
oof.json
analytics.json
all-performance.json
memory-2.json
windows-server-2016.json
generic.json
AP.json
OOPS.json
opp1.json
opp2.json
Performance.json
Windows Server 2016.json
memory.json
Note sure why I get empty cells in the second column. Can anyone help me with tis please.
2

The best way to handle excel files in PowerShell is via ImportExcel module. It should server most of the usecases.

3

Your problem is that you’re running two separate loops for each of your datasets and both of those loops are adding rows to your table.

Your second loop either needs to be adding the data to row1, column2 or you need to use a single loop that populates row1: column1, column2; row2: column1, column2 etc.

 

4

Hi Matt,

Thanks for your reply. I have tried using a single loop but could not get desired result. Any example script can hep :slight_smile:

5

I stuck with two loops:

# Define the test data
$default = 'oof.json','analytics.json','all-performance.json','memory-2.json','windows-server-2016.json','generic.json'
$custom = 'AP.json','OOPS.json','opp1.json','opp2.json','Performance.json','Windows Server 2016.json','memory.json'

# Work out how many rows we need
if ($default.Count -gt $custom.Count) {

    $rows = $default.Count

}

else {
    
    $rows = $custom.Count

}

# Table name
$tableName = “files”

# Create Table object
$table = New-Object system.Data.DataTable “$tableName”
 
# Define Columns
$col1 = New-Object system.Data.DataColumn "DefaultFiles",([string])
$col2 = New-Object system.Data.DataColumn "CustomFiles",([string]) 

# Add the Columns
$table.columns.add($col1)
$table.columns.add($col2)

# Add the rows
for ($r = 0; $r -lt $rows; $r++) {

    $table.Rows.Add() | Out-Null

}

#Populate the table
for ($i = 0; $i -lt $default.Count; $i++) {

    $table.Rows[$i][0] = $default[$i]

}

for ($j = 0; $j -lt $custom.Count; $j++) {

    $table.Rows[$j][1] = $custom[$j]

}

#Export the table
$table  | Export-Csv E:\Temp\newtable.csv -NoTypeInformation