Author Posts

October 16, 2017 at 5:45 pm


I have a script I created to pull data from a database and export it to a CSV. I am trying to see if It's possible to have my script import that CSV and export the data within some of the cells into a NEW CSV. One of the cells contains different lines that we would like to use for another report.
The cell looks like this:
~~Task: TO
~~Location: LOC
~~Type: YN
~~Network: Net
~~Acknowledge Time: 171016 1300
~~Start Time: 171016 1300

What I would like to do is create a headers with the names after the tilde and fill the cells data in with what follows the colon.
so it would be:
Task | Location | Etc..
TO | LOC | etcstuff

Any help is appreciated, I've never had to pull data within cells like this before so this part is new to me.

October 16, 2017 at 6:05 pm

Yeah, so the easy part assumes that you've parsed the information in the cell into individual variables.

function ConvertTo-OtherReport {

 $lines = $inputObject -split "`n"
 $props = @{}
 foreach ($line in $lines) {
  $pieces = ($line -replace "~~","") -split ":"
  $props.Add($pieces[0].Trim(), $pieces[1].Trim())
 New-Object -Type PSObject -Props $props


You'd use this...

Import-CSV whatever.csv | Select ColumnIWantToParse | ConvertTo-OtherReport | Export-CSV new file.csv

I'm kind of winging it here, but if you have any questions about any of those bits that will help, please ask.

October 16, 2017 at 6:06 pm

You would read the CSV with import-csv cmdlet and write the new one with export-csv.
Have you written any part of the script yet? Please post.
Is 'etc' to be the concatenation of 'Type,Network,Acknowledge Time,Start Time'?

October 16, 2017 at 6:22 pm

I don't think it is parsed into variables. The script pulls the data from a Remedy Database and exports it to the CSV, if I would just do it all in one script without having to import then export the CSV that would be great but I don't think it would work for some reason. 'Etc' was just the concatenation. Here is the script:

## Login Information - Using Token Credentials doesn't work yet - have to use Remedy Username and Password
$id = Get-Credential "ONE\$("noblek")"
$userId = $id.UserName.split("\")[1].tolower()
$password = $id.GetNetworkCredential().Password 
    ##### Start the database connection and set up environment
    $DbString="DSN=AR SYSTEM ODBC Driver;UID=$userID;PWD=$password"
    #Need to have the path to the BMC DLL File
    add-type -path 'C:\BMC.ARSystem.dll'
    #Connection Strings
    $DBConnection=New-Object System.Data.Odbc.OdbcConnection ($SelectStatement)
    $DBCommand=New-Object System.Data.Odbc.OdbcCommand

#Export CSV FilePath
$exporCsvPath = "C:\test.csv" 
#Prompt user start and end date in yyyy-mm-dd format
$startDate = Read-Host "Start Date yyyy-mm-dd"
$endDate = Read-Host "End Date yyyy-mm-dd"
Write-Host "Running Query"

    Assigned_Group ='Group_1'
    AND Reported_Date > {ts '$startDate 00:00:00.00'} AND
    Reported_Date  {ts '$startDate 00:00:00.00'} AND
    Reported_Date  {ts '$startDate 00:00:00.00'} AND
    Reported_Date  {ts '$startDate 00:00:00.00'} AND
    Reported_Date < {ts '$endDate 11:59:59.00'}
## Execute Query
 ##Export to CSV
    $UserTable | Export-CSV $exporCsvPath -NoTypeInformation

So the script queries the database pulls the data for those groups and exports it to the CSV. The Resolution is the field that has all of the information I am trying to split up.

October 16, 2017 at 6:23 pm

Yeah, the little function I posted actually does the parsing. And my function doesn't presume you have a CSV file to start with; you could pipe the objects directly from $UserTable also. Did you review the function I posted above?

October 16, 2017 at 7:12 pm

Editing my post from yesterday cause I didn't have any clue what I was talking about. It actually worked. I had to put -ExpandProperty to get it to display correctly.

However I keep getting the error:

You cannot call a method on a null-valued expression.
At C:\Users\noblek\Desktop\This ODBC Actually Works – Copy.ps1:19 char:3
+ $props.Add($pieces[0].Trim(), $pieces[1].Trim())
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

Is there any way to combine the data that is ran in the function with some/most of the data that is in the $UserTables function?