using get-content from csv file, but wanting to format to word table

Hi all,

The companyn I work for has a service sheet that is in Word, with loads of tables (just how it’s done)…my issue is, I can create tables from Powershell to Word, but can’t see a way around using get-content (which has no way to format the contents) whih is causing me problems…i would like to output the get-content into 1 cell, but formatted in column lines…Is ther another way to do ths, that I’m missing? Any help greatly appreciated…

# Disk Info:
$CSV = "C:\Test\Disk.csv"
$Disk =(Get-WmiObject -Class Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3} |
 Sort-Object -Property Name | 
    Select-Object Name, 
        @{"Label"="  Size(GB)";"Expression"={"{0:N}" -f ($_.Size/1GB) -as [float]}}, 
        @{"Label"="  Free(GB)";"Expression"={"{0:N}" -f ($_.FreeSpace/1GB) -as [float]}}, 
        @{"Label"="  %Free";"Expression"={"{0:N}" -f ($_.FreeSpace/$_.Size*100) -as [float]}} | Export-CSV  -delimiter  “,” $CSV -noTypeInformation)


(Get-Content C:\Test\Disk.csv) | % {$_ -replace '"', ""} | out-file -FilePath C:\Test\Disk.csv -Force -Encoding ascii
(Get-Content C:\Test\Disk.csv) | % {$_ -replace ',', "     "} | out-file -FilePath C:\Test\Disk.csv -Force -Encoding ascii
$Info = (Get-Content $CSV) 
$word = New-Object -ComObject word.application
$word.visible = $true
$word.application.DisplayAlerts = 0
$doc = $word.documents.add()
$selection = $word.selection
$selection.Font.Name = "Arial"
$selection.Font.Size = 14
$selection.Font.Bold = $true;
$selection.Font.Color = 255
$selection.typeText("This is a Test")
$selection.paragraphFormat.Alignment = 1
$selection.TypeParagraph()
$selection.Font.Name = "Arial"
$selection.Font.Size = 10
$selection.Font.Color = 1
$selection.paragraphFormat.Alignment = 2
$selection.Font.Bold = $false;
$selection.TypeText($fDATE);$selection.TypeParagraph()
$selection.Font.Name = "Arial"
$selection.Font.Size = 11
$selection.paragraphFormat.Alignment = 1
$selection.TypeParagraph()
$number_Of_Rows = 3;$number_Of_Columns = 3
$paragraph = $doc.Content.Paragraphs.Add()
$Range = $paragraph.Range
$Table = $doc.Tables.Add($Range,$number_Of_Rows,$number_Of_Columns)
$Table = $doc.Tables.item(1)
$Table.Borders.OutsideLineStyle = 1
$Table.Borders.InsideLineStyle = 1
$Table.shading.BackgroundPatternColor = $NTS
$Table.Cell(1,1).Range.Font.Bold = $True
$Table.Cell(1,1).Range.Font.Italic = $True
$Table.Cell(1,1).Range.Font.Name =”Calibri”
#$Table.Cell(1,1).Range.shading.BackgroundPatternColor = $NTS
#$Table.Cell(1,3).Range.shading.BackgroundPatternColor = $NTS
$Table.Cell(1,3).Range.Font.Name =”Calibri”
$Table.Cell(1,2).Range.Font.Bold = $True
$Table.Cell(1,3).Range.Font.Bold = $True
$Table.Cell(1,1).PreferredWidthType = 3
$Table.Cell(1,1).Width = 50
$Table.Cell(1,2).PreferredWidthType = 3
$Table.Cell(1,2).Width = 75
$Table.Cell(1,3).PreferredWidthType = 3
$Table.Cell(1,3).Width = 200
$Table.Cell(2,1).PreferredWidthType = 3
$Table.Cell(2,1).Width = 50
$Table.Cell(2,2).PreferredWidthType = 3
$Table.Cell(2,2).Width = 75
$Table.Cell(2,3).PreferredWidthType = 3
$Table.Cell(2,3).Width = 200
$Table.Cell(3,1).PreferredWidthType = 3
$Table.Cell(3,1).Width = 50
$Table.Cell(3,2).PreferredWidthType = 3
$Table.Cell(3,2).Width = 75
$Table.Cell(3,3).PreferredWidthType = 3
$Table.Cell(3,3).Width = 200
$Table.Cell(1,1).Range.Text = "$Date"
$Table.Cell(1,2).Range.Text = "COL 2"
$Table.Cell(1,3).Range.Text = "$Info"
$Table.Cell(1,3).Range.Font.Name = "$Arial"
$Table.Cell(1,3).Range.Font.Size = 10
$selection.Font.Bold = $false;
$Table.Cell(2,1).Range.Text = "A Value"
$Table.Cell(2,2).Range.Text = "B Value"
$Table.Cell(2,3).Range.Text = "C Value"
$Table.Cell(3,1).Range.Text = "D Value"
$Table.Cell(3,2).Range.Text = "E Value"
$Table.Cell(3,3).Range.Text = "F Value"
$selection.EndKey(6)
$selection.TypeParagraph()

As you can see, I’ve tried ‘formatting’ spaces to achieve the results, but a bit flakey to be honest!
I would look into Excel output, to create the tables, then transfer to Word, but haven’t had any experience with Excel and Powershell - yet!

Would it not be easier to use Import-CSV, so you’re getting structured data to work with in the first place? As it is, it’s just all text-munging.

Hi Don, thanks for the quick response! I tried Import-CSV, but it either returns the CSV location, or outputs nothing to the table cell - which I couldn’t work out why! I added -Header too, for Name, etc, but that didn’t work for me! Just learning with the output to Word tables too!

oh, and great new look to the website!

Well, so, Import-CSV isn’t just bringing in a wedge of text.

$rows = Import-CSV whatever.csv

ForEach ($row in $rows) {

$row.column1 # accessing the contents of "column1"

$Table.Cell(2,1).Range.Text = $row.column1

$Table.Cell(2,2).Range.Text = $row.fred

$Table.Cell(2,3).Range.Text = $row.ginger

}

 

Assuming your CSV has headers named “Column1,” “Fred,” and “Ginger.” In terms of doing anything programmatic in Word, I’m not your guy. That stuff is of the devil. If I need to make reports, I stick the data in a SQL Server table and make the report in SQL Server Reporting Services, which has a drag n drop report designer. Then I schedule the report to run, and schedule scripts to populate the table. Even wrote a book about it :).

Thanks Don, that approach certainly looks tidier, and makes data transferable in a better way. I’ll investigate further - all aprt of the learning curve!

Good idea with SQL - just a shame I haven’t got access to that myself!

 

SQL Express is free :). It’s what I used in the book. Which is also free.

Normal
0

false
false
false

EN-GB
X-NONE
X-NONE

/* Style Definitions */
table.MsoNormalTable
{mso-style-name:“Table Normal”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:"";
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin-top:0cm;
mso-para-margin-right:0cm;
mso-para-margin-bottom:8.0pt;
mso-para-margin-left:0cm;
line-height:107%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:“Calibri”,sans-serif;
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:“Times New Roman”;
mso-bidi-theme-font:minor-bidi;
mso-fareast-language:EN-US;}

Name

Size(GB)

Free(GB)

%Free

C:

222.92

156.35

70.14

D:

14.32

1.72

12.03

# General settings
CLS
$fDATE = Get-Date -Format "dddd, MMMM, dd, yyyy"
$Date = ((Get-Date).ToString('dd-MM-yyyy'))
#Colour Settings
$Red = 251
$Green = 212
$Blue = 180
$NTS = [long]($Red + ($Green * 256) + ($Blue * 65536))
#Table cell width settings
$TC1 = 40
$TC2 = 60
$TC3 = 75
$TC4 =75
# Disk Info:
$CSV = "C:\Test\Disk.csv"
$Disk =(Get-WmiObject -Class Win32_LogicalDisk |
Where-Object {$_.DriveType -eq 3} |
Sort-Object -Property Name | 
Select-Object Name, `
@{"Label"="Size(GB)";"Expression"={"{0:N}" -f ($_.Size/1GB) -as [float]}}, `
@{"Label"="Free(GB)";"Expression"={"{0:N}" -f ($_.FreeSpace/1GB) -as [float]}}, `
@{"Label"="%Free";"Expression"={"{0:N}" -f ($_.FreeSpace/$_.Size*100) -as [float]}} | Export-CSV-delimiter“,” $CSV -noTypeInformation)
#(Get-Content C:\Test\Disk.csv) | % {$_ -replace '"', ""} | out-file -FilePath C:\Test\Disk.csv -Force -Encoding ascii
#(Get-Content C:\Test\Disk.csv) | % {$_ -replace ',', " \ "} | out-file -FilePath C:\Test\Disk.csv -Force -Encoding ascii
#$Info = (Get-Content $CSV) 
$word = New-Object -ComObject word.application
$word.visible = $true
$word.application.DisplayAlerts = 0
$doc = $word.documents.add()
$selection = $word.selection
$selection.Font.Name = "Arial"
$selection.Font.Size = 14
$selection.Font.Bold = $true;
$selection.Font.Color = 255
$selection.typeText("This is a Test")
$selection.paragraphFormat.Alignment = 1
$selection.TypeParagraph()
$selection.Font.Name = "Arial"
$selection.Font.Size = 10
$selection.Font.Color = 1
$selection.paragraphFormat.Alignment = 2
$selection.Font.Bold = $false;
$selection.TypeText($fDATE);$selection.TypeParagraph()
$selection.Font.Name = "Arial"
$selection.Font.Size = 11
$selection.paragraphFormat.Alignment = 1
$selection.TypeParagraph()
$number_Of_Rows = 3;$number_Of_Columns = 4
$paragraph = $doc.Content.Paragraphs.Add()
$Range = $paragraph.Range
$Table = $doc.Tables.Add($Range,$number_Of_Rows,$number_Of_Columns)
$Table = $doc.Tables.item(1)
$Table.Borders.OutsideLineStyle = 1
$Table.Borders.InsideLineStyle = 1
$Table.shading.BackgroundPatternColor = $NTS
$Table.Cell(1,1).Range.Font.Bold = $True
$Table.Cell(1,1).Range.Font.Italic = $True
$Table.Cell(1,1).Range.Font.Name =”Calibri”
#$Table.Cell(1,1).Range.shading.BackgroundPatternColor = $NTS
#$Table.Cell(1,3).Range.shading.BackgroundPatternColor = $NTS
$rows = Import-CSV $CSV -Header column1,fred,ginger,henry
$R = 1
ForEach ($row in $rows) {
$row.column1 # accessing the contents of "column1"
$Table.Cell($R,1).PreferredWidthType = 3
$Table.Cell($R,1).Width = $TC1
$Table.Cell($R,1).Range.Text = $row.column1
$Table.Cell($R,2).PreferredWidthType = 3
$Table.Cell($R,2).Width = $TC2
$Table.Cell($R,2).Range.Text = $row.fred
$Table.Cell($R,3).PreferredWidthType = 3
$Table.Cell($R,3).Width = $TC2
$Table.Cell($R,3).Range.Text = $row.ginger
$Table.Cell($R,4).PreferredWidthType = 3
$Table.Cell($R,4).Width = $TC2
$Table.Cell($R,4).Range.Text = $row.henry
$R = ($R+1)
}
$selection.EndKey(6)
$selection.TypeParagraph()

Thanks Don, have amended and got the table output, just needs tweaking a bit now!

Anyone that can help getting this table inside the cell of a main table? Guess that’d be nested tables, but haven’t found anything that works -yet!