Author Posts

October 24, 2017 at 7:03 pm

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?

October 24, 2017 at 8:41 pm

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