Export data in CSV cell to new CSV

PowerShell Help
1

Hello,

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.

2

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

function ConvertTo-OtherReport {
 [CmdletBinding()]
 Param(
  [Parameter(ValueFromPipeline=$True)]
  [object]$InputObject
 )
 PROCESS {

 $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.

3

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’?

4

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
    $DBConnection.ConnectionString=$DbString
    $DBConnection.Open()
    $DBCommand.Connection=$DBConnection

#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"

#Query
    $DBCommand.CommandText=
"SELECT
    Incident_Number,
    AssigneeEffortDurationSeconds,
    Description, 
    Detailed_Decription, 
    Resolution, 
    Status, 
    Last_Resolved_Date, 
    Reported_Date, 
    Effort_Hour, 
    Effort_Minute,
    Effort_Second, 
    Assignee, 
    Assigned_Group, 
    Priority
FROM
    HPDAssignLog_n_INC
WHERE 
    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
    $DBResult=$DBCommand.ExecuteReader()
    $UserTable=New-Object system.data.datatable
    $UserTable.load($DBResult) 
    $DBConnection.Close()
 
 ##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.

5

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?

6

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?