Powershell; Import txt file, format it and export it to csv

1
votes

I'm in the process of automating my new hire account creation in AD possess. I have created my scripts to import a csv file and that all works great. But now I am trying to automate it even further by just copying the contents of the email HR sends me into a txt file then have powershell format each line as a new column then export it to a csv file I can run my other script on (eventually combining them into just 1 script) The email format comes in looking like this.

Username: Test User
Title: Sales
Department: Sales
Manager: Manager's Name
Similar User: CoWorker's Name`

I just want to copy and paste that into notepad and save it. then have powershell import it, format it like it is below and then export it.

   Username   Title  Department  Manager         Similar User
   Test User  Sales  Sales       Manager's Name  CoWorker's Name

I have been trying the import-csv and get-content commands but I am having no luck getting it formatted the way I need it. Any one have any suggestions?

1
powershellcsv

1 Answers

5
votes

A simple approach is to use ConvertFrom-StringData (the solution assumes that each line only contains exactly one :, and that the input file is named user.txt; PSv3+):

$userObj = [pscustomobject] ((Get-Content -Raw user.txt) -replace ':', '=' |
  ConvertFrom-StringData)

Since ConvertFrom-StringData requires = as the separator between the key and its value, -replace is used to replace : with =.
Since ConvertFrom-StringData returns a hashtable, cast [pscustomobject] is used to convert it to a custom object.

Once the information has been parsed into custom object $userObj, it's easy to process it further:

Further processing as data:

To save the information to CSV file out.csv:

$userObj | Export-Csv -NoTypeInformation -Encoding Utf8 out.csv

This yields:

"Manager","Similar User","Department","Username","Title"
"Manager's Name","CoWorker's Name","Sales","Test User","Sales"

Further processing to reformat for display purposes:

The default formatting gives you this (which just happens to be very similar to the input:

Manager      : Manager's Name
Similar User : CoWorker's Name
Department   : Sales
Username     : Test User
Title        : Sales

$userObj | Format-Table yields:

Manager        Similar User    Department Username  Title
-------        ------------    ---------- --------  -----
Manager's Name CoWorker's Name Sales      Test User Sales

To get the desired field order: $userObj | Format-Table 'Username', 'Title', 'Department', 'Manager', 'Similar User':

Username  Title Department Manager        Similar User   
--------  ----- ---------- -------        ------------   
Test User Sales Sales      Manager's Name CoWorker's Name

If you want to get rid of the divider line (---- ...) and of leading and trailing empty lines:
((($userObj | Format-Table 'Username', 'Title', 'Department', 'Manager', 'Similar User') | Out-String -Stream) | ? { $_ -notmatch '^-|^$' })

Username  Title Department Manager        Similar User   
Test User Sales Sales      Manager's Name CoWorker's Name