Export data in CSV cell to new CSV

This topic contains 5 replies, has 3 voices, and was last updated by  Keith 1 month ago.

  • Author
    Posts
  • #82217

    Keith
    Participant

    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.

  • #82219

    Don Jones
    Keymaster

    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.

  • #82220

    Sam Boutros
    Participant

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

  • #82226

    Keith
    Participant

    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.

  • #82229

    Don Jones
    Keymaster

    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?

    • #82237

      Keith
      Participant

      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?

You must be logged in to reply to this topic.