Export data in CSV cell to new CSV

Welcome Forums General PowerShell Q&A Export data in CSV cell to new CSV

This topic contains 5 replies, has 3 voices, and was last updated by

1 year, 4 months ago.

  • Author
  • #82217

    Points: 0
    Rank: Member


    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

    Points: 1,811
    Helping HandTeam Member
    Rank: Community Hero

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

    function ConvertTo-OtherReport {
     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

    Points: 157
    Helping Hand
    Rank: 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

    Points: 0
    Rank: Member

    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.

  • #82229

    Points: 1,811
    Helping HandTeam Member
    Rank: Community Hero

    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

      Points: 0
      Rank: Member

      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?

The topic ‘Export data in CSV cell to new CSV’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort