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

[JasonL Participant Points: 30Rank: Member]

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!

[Don Jones Keymaster Points: 1,619Rank: Community Hero]

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.

[JasonL Participant Points: 30Rank: Member]

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!

[JasonL Participant Points: 30Rank: Member]

oh, and great new look to the website!

[Don Jones Keymaster Points: 1,619Rank: Community Hero]

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 :).

[JasonL Participant Points: 30Rank: Member]

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!

 

[Don Jones Keymaster Points: 1,619Rank: Community Hero]

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

[JasonL Participant Points: 30Rank: Member]

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!

 

[Author]

Posts