Parsing data into a .csv file

This topic contains 1 reply, has 2 voices, and was last updated by  Curtis Smith 3 weeks, 5 days ago.

  • Author
    Posts
  • #82766

    Brendan
    Participant

    I am working on a PowerShell program to parse some financial files we are getting. We get the data in one txt file and I need to export it as a csv in specified rows and columns. I have gotten the txt file parsed, and all prepped, so I need to walk through each row in the parsed file and create about 15 new columns in a new file.

    For example,

    Parsed file,

    Row1: A,B,C,D
    Row2: A,B,C,D
    Row3: A,B,C,D

    New File,
    Row1: Static text,(A+B),Blank,Blank,Blank,(C+".0000.000.1"),Blank,Blank,(C+D+"477883.0000.334")
    Row2: Static text,(A+B),Blank,Blank,Blank,(C+".0000.000.1"),Blank,Blank,(C+D+"477883.0000.334")
    Row3: Static text,(A+B),Blank,Blank,Blank,(C+".0000.000.1"),Blank,Blank,(C+D+"477883.0000.334")

    Something like that, where the new file is made up of more columns of data, made up of the data from the parsed file, plus static bits that have to be there. I have used PowerShell a bit, but all for actions and tasks, never to parse and create files like this, so I am sort of at a loss.

    My idea was something like

    ForEach ($row in $parsedFile) {
    
    $colA = "Static text"
    $colB = ""
    $colC = ""
    $colD = $row.Header1 + $row.Header2
    
    $doneCol = $colA + $colB + $colC + $colD
    
    $doneCol | export-csv	-Append -Path C:\doneFile.csv
    
    }

    Does that make any sense? 🙂 Is that even close to the right way to do it?

  • #82787

    Curtis Smith
    Participant

    There are several way sto accomplish your task. Here is one possibility.

    Import-Csv "D:\New Text Document.txt" |
    ForEach-Object {
        [pscustomobject]@{
            A = "Static text"
            B = [int]$_.A + [int]$_.B
            C = ""
            D = ""
            E = ""
            F = $_.C + ".0000.000.1"
            G = ""
            H = ""
            I = [string]([int]$_.C + [int]$_.D) + "477883.0000.334"
        }
    } | Export-Csv Output_File.csv -NoTypeInformation 

You must be logged in to reply to this topic.