Parsing data into a .csv file

Welcome Forums General PowerShell Q&A Parsing data into a .csv file

This topic contains 1 reply, has 2 voices, and was last updated by

 
Participant
1 year ago.

  • Author
    Posts
  • #82766

    Participant
    Points: 0
    Rank: Member

    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

    Participant
    Points: 0
    Rank: Member

    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 

The topic ‘Parsing data into a .csv file’ is closed to new replies.