Export data in CSV cell to new CSV


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.

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.

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

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
    $UserTable=New-Object system.data.datatable
 ##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.

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?

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?