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

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

 
Participant
3 months ago.

  • Author
    Posts
  • #108082

    Participant
    Points: 30
    Rank: 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!

  • #108083

    Keymaster
    Points: 1,619
    Helping HandTeam Member
    Rank: 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.

  • #108085

    Participant
    Points: 30
    Rank: 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!

  • #108088

    Participant
    Points: 30
    Rank: Member

    oh, and great new look to the website!

  • #108089

    Keymaster
    Points: 1,619
    Helping HandTeam Member
    Rank: 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 :).

  • #108091

    Participant
    Points: 30
    Rank: 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!

     

  • #108094

    Keymaster
    Points: 1,619
    Helping HandTeam Member
    Rank: Community Hero

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

  • #108103

    Participant
    Points: 30
    Rank: 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!

     

The topic ‘using get-content from csv file, but wanting to format to word table’ is closed to new replies.