Welcome › Forums › General PowerShell Q&A › using get-content from csv file, but wanting to format to word table
This topic contains 7 replies, has 2 voices, and was last updated by
-
AuthorPosts
-
August 9, 2018 at 9:36 pm #108082
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! -
August 9, 2018 at 9:38 pm #108083
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.
-
August 9, 2018 at 9:42 pm #108085
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!
-
August 9, 2018 at 9:44 pm #108088
oh, and great new look to the website!
-
August 9, 2018 at 9:45 pm #108089
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 :).
-
August 9, 2018 at 9:52 pm #108091
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!
-
August 9, 2018 at 9:53 pm #108094
SQL Express is free :). It's what I used in the book. Which is also free.
-
August 9, 2018 at 10:44 pm #108103
Normal
0false
false
falseEN-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!
-
AuthorPosts
The topic ‘using get-content from csv file, but wanting to format to word table’ is closed to new replies.