Import-CSV | Export-HTML but format issues

This topic contains 8 replies, has 4 voices, and was last updated by Profile photo of Rob Simmers Rob Simmers 1 year, 8 months ago.

  • Author
    Posts
  • #11949
    Profile photo of Jake Sully
    Jake Sully
    Participant

    Hello – I am trying to import a csv file that is generated by some database administrators and then I have to export the csv as an html email.

    I have the email working and the import is OK, but the display is not good. There are a couple issues and I'm hoping someone can help me make this a "nicer" report.

    1. The csv has the 1st row as an empty row, not sure why as I don't generate it, that's done by some old batch files or vb scripts.
    2. The 2nd row has an entry in the 1st column titled "#Name?" and it appears the csv file believes that's a formula
    3. The format of the html email is "ok" but the columns are very tight meaning that the dates in the columns consume all the space and it just isn't nicely displayed.

    Here's a sample portion of the csv file and then a portion of the html email.

    #NAME?
    hostname SQLInstance Database_Name Status Start_Time End_Time Backup _Size
    Server1 reports sysdba Yes 12/09/2013 23:09 12/09/2013 23:16 3730.31
    server2 messedup Yes 12/09/2013 23:16 12/09/2013 23:16 2.08
    server3 working powershell Yes 12/09/2013 23:16 12/09/2013 23:18 1091.11

  • #11950
    Profile photo of Don Jones
    Don Jones
    Keymaster

    You'll have to figure out how to take the extra stuff out of the CSV file – PowerShell won't do that for you. One way is to use Get-Content to load the file, instead of Import-CSV; pipe the content to Select-Object and have that skip the first 2 rows (-Skip parameter), and pipe what's left to ConvertFrom-CSV, and then to ConvertTo-HTML.

    The HTML produced by ConvertTo-HTML is "clean." That is, it doesn't contain any formatting. You can use the -Head switch to insert an HTML STYLE section, which allows you to apply formatting. You're going to have to learn some Cascading Style Sheet (CSS) language. You might also look into "Creating HTML Reports in PowerShell" (click the Newsletter tab here, it's a free ebook on that page). I wrote a module that makes it a bit easier to apply more detailed formatting, and the book explains a bit of CSS. But there's no way around knowing some CSS if you want to apply formatting. You should also know that most email clients don't support the full range of formatting CSS can offer, so you'll need to experiment a bit until you find something you like that also works. w3schools.com has a lot of good CSS tutorials and references, and that ebook is a starting point.

  • #11953
    Profile photo of Jake Sully
    Jake Sully
    Participant

    Hi Don

    Thank you as always, your training vids and books are helping quite a bit. I got rid of the #Name? mark from appearing by adding this into the script, but I don't know why the name of the csv file is appearing or else I'm mistaken. When I started the script was failing do to the csv file having #Name? in the 2nd row, 1st column. I read a bunch of info and went with doing import-csv -path c:\temp\somefile.csv -header header1, header2, etc.

    Now the name of the file is showing up in the html report for some reason. Also if I could format the dates better then it might be a quick fix as they are on the only columns that are really not good to look at.

    I did try the get-content and convertfrom-csv method, but I then only received a bunch of posh info, nothing that I expected in the report. Here's a copy of the script, if you can maybe point me as to how to do the get-content and convertfrom-csv that would be great. I added a variable for get-content, then piped that to convertfrom-csv, and then used a variable that I piped to exportto-html, which failed.

    $html = "

    "
    $Date = Get-Date -format d
    $sqlservers = Import-CSV 'c:\temp\test.csv' -Header "Servername","InstanceName","Database_Name","Status","Start_Backup","Last_Backup","Bkp_Size" | ConvertTo-HTML -head $html -body "

    SQL Server Backup Report $date

    "
    $toemail = "JakeS@devlab.local"
    $fromemail = "JakeS@devlab.local"
    $Subject = 'SQL Daily Report'
    $server = "email.devlab.local"
    $message = @"
    $sqlservers
    "@
    Send-MailMessage -smtpServer $server -to $toemail -from $fromemail -subject $Subject -body $message -BodyAsHtml

    "Servername","InstanceName","Database_Name","Status","Start_Backup","Last_Backup","Bkp_Size"

  • #11954
    Profile photo of Don Jones
    Don Jones
    Keymaster

    So, look. If this is my CSV file:

    #NAME?
    hostname SQLInstance Database_Name Status Start_Time End_Time Backup _Size
    Server1	 reports	 sysdba	 Yes 12/09/2013 23:09	 12/09/2013 23:16	 3730.31
    server2	 messedup	 Yes 12/09/2013 23:16	 12/09/2013 23:16	 2.08
    server3	working	powershell	Yes 12/09/2013 23:16	 12/09/2013 23:18	 1091.11
    

    Meaning, one bogus line, I do this:

    Get-Content file.csv | Select -Skip 1 | ConvertFrom-CSV | ConvertTo-HTML
    

    The output is fine. I'm not sure where you're picking up the filename from. You're not sharing with me the actual command you ran or the output it produced, so it's a little tough to troubleshoot on this end.

    Good job on the CSS. That's exactly how to do it. You do have to be a bit careful with ConvertTo-HTML, though. -Body can override what you're piping in. You probably want to use -PreContent, which appends content BEFORE the table that the cmdlet will create; -PostContent would let you do a footer. Also, try this:

    Get-Content file.csv | Select -Skip 1 | ConvertFrom-CSV | ConvertTo-HTML -Head "whatever" -PreContent "whatever" | Out-String
    

    That Out-String will eliminate the need to jam the result into a here-string. ConvertTo-HTML produces an array of strings; Out-String will consolidate them into a single hunk.

  • #11955
    Profile photo of Jake Sully
    Jake Sully
    Participant

    Here's a sample of what's produced, I'm not sure what you meant in terms of the command I'm using. I'm not creating the csv file, that's being done by some batch scripts and vb scripts that the database group uses, I'd like to convert it all to POSH but that's another story.

    I'm not sure if this will be that helpful, I didn't see how to paste this in so it would show up as HTML content. But the columns for Start and End times are the biggest issue as the date fills the whole cell in the HTML report that is in the email body, it just looks crammed together. Then I have no idea why the name of the csv I'm doing an import of is getting put in this report, is it actually reading that #Name? value and using it even though I'm specifying the column headers that I want? That's not a show stopped but I'd like to know why it's there. If I can figure out how to make the columns wider so there's some spacing or change the date format that would be helpful. I don't know how I would do that given the csv if already generated for me.

    SQL Server Backup Report 12/11/2013
    Servername InstanceName Database_Name Status Start_Backup Last_Backup Bkp_Size

    ———- Testresults.CSV
    Servername Instance Database_Name Status Start_Time End_time Backup_Size
    Server1 TestOne DB1 Yes 2013-12-09 23:09:46 2013-12-09 23:16:24 3730.31
    Server2 Yes 2013-12-09 23:16:25 2013-12-09 23:16:26 2.08

  • #11956
    Profile photo of Don Jones
    Don Jones
    Keymaster

    You can attach an HTML file if you give it a TXT filename extension.

    I'm fine with the CSV being produced elsewhere; you'd said that "I got rid of the #Name? mark from appearing by adding this into the script, but I don't know why the name of the csv file is appearing or else I'm mistaken." But I'm not seeing the command you ran where "the name of the CSV file is appearing," nor the output. When I run the commands I showed you, I'm getting the desired output.

    You can also try using the HTML "PRE" tags here in the forums, to format your code. Makes communicating these things a bit more precise.

    I think you need to start over a bit. Look at the code I gave you in my previous post. Let's ignore the formatting for right now – one thing at a time. If you do this:

    Get-Content filename.csv | Select -Skip 2 | ConvertFrom-CSV | ConvertTo-HTML -PreContent "HEADING" | Out-String
    

    I put "-Skip 2" because in your first post, you said the CSV file has a blank line and then the #Name? line, so I want to skip those.

    So, does that produce the desired output? Aside from the formatting? Let's get this working first, and then I can help you with the formatting, I promise.

  • #11957
    Profile photo of Don Jones
    Don Jones
    Keymaster

    It might also help if you attached a screen shot of the not-good-looking-email message body. I'm not entirely sure what you're describing, in terms of the formatting you don't like. You can attach PNG files as well as TXT files.

  • #30470
    Profile photo of Karthik Durairajan
    Karthik Durairajan
    Participant

    Just wondering if this thread is still active. And that if I can resurrect it, Don. I'm trying to do exactly the same thing as Jake but your instructions don't seem to work? Thanks.

  • #30489
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    You should start your own thread and provide examples of the data you are working with (e.g. CSV), code you've tried, errors and what you expect to happen.

You must be logged in to reply to this topic.