Convert fixed width txt file to CSV / set-content or out-file -append?

Question

Input file is a fixed-width txt file. My client normally opens it in Excel and manually specifies the column breaks. I'm hoping to replace certain blank spaces with a comma, so that I can parse as CSV and save as XLS or whatever.

$columBreaks = 20, 35, 50, 80, 100, 111, 131, 158, 161, 167, 183
[array]::Reverse($columBreaks) #too lazy to re-write array after finding out I need to iterate in reverse

$files = get-childitem ./ |where-object {$_.Name -like "FileFormat*.txt"}

foreach($file in $files)
{
    $name = $file.Name.split(".")
    $csvFile = $name[0]+".csv"
    if (!(get-childitem ./ |where-object {$_.Name -like $csvFile})) #check whether file has been processed
    { 
        $text = (gc $file) 
        foreach ($line in $text)
        {
           foreach ($pos in $columBreaks)
            {
                #$line.Substring($char-1,3).replace(" ", ",")
                $line = $line.Insert($pos,",")
                #out-file -append?
            }
        } 
    }
    #set-content?
}

So what's the most efficient way to write this content out? I had hoped to use set-content, but I don't think that's possible since we're processing line by line, so I think I would either have to build an array of lines for set-content, or use write-out -append for each iteration. Is there a more efficient way to do this?

Bangweulu

Answer 1

Set-Content should work fine with some minor adjustments. Here is an example of how it should work (this is everything within your outer foreach loop):

$csvFile = $file.BaseName
    if (!(get-childitem ./ |where-object {$_.Name -like $csvFile})) #check whether file has been processed
    { 
        (gc $file | foreach {
                $_.Insert($columBreaks[0],",").Insert($columBreaks[1],",").Insert($columBreaks[2],",").`
                Insert($columBreaks[3],",").Insert($columBreaks[4],",").Insert($columBreaks[5],",").`
                Insert($columBreaks[6],",").Insert($columBreaks[7],",").Insert($columBreaks[8],",").`
                Insert($columBreaks[9],",").Insert($columBreaks[10],",")
            }) | set-content $csvFile #note parenthesis around everything that gets piped to set-content
    }

By the way, instead of splitting the filename on the '.', you can just get the name without the extension by using $file.BaseName:

$csvFile = $file.BaseName + ".csv"

Gelatinous

Thanks for the basename tip. The set-content gives me an empty pipe error. – Bangweulu

I missed your if statement at first. You should just be able to move the set-content bit up within the if block (immediately following the 'foreach ($line in $text)' – Gelatinous

I don't think piping from a foreach works at all. I've seen several examples that use the foreach-object, but I struggled with converting the inner loops in the format needed for that. – Bangweulu

It does work, but you need to wrap parenthesis around everything you pipe to it (which I neglected to mention). I've edited the response to show how you can make it work. I replaced the loop over the column breaks to a sequential call to the insert function as this was causing duplicated lines in my testing. YMMV. – Gelatinous

I deployed to production using out-file append. I'm giving you the check for the points. I actually wound up with about 45 fields in the file, so I created a var for each one using substring, then concatenated them with a comma between each one. It was ugly, but it got the job done. – Bangweulu

Answer 2

I would think this comes up a lot. Here's an example that actually goes overboard and turns the fixed width file into objects. Then it's simple to export that to a csv. This should work for converting legacy commands like netstat as well.

$cols = 0,19,38,59,81,97,120,123 # fake extra column at the end, assumes all rows are that wide, padded with spaces
$colsfile = 'columns.txt'
$csvfile = 'cust.csv'

$firstline = get-content $colsfile | select -first 1
$headers = for ($i = 0; $i -lt $cols.count - 1; $i++) {
  $firstline.substring($cols[$i], $cols[$i+1]-$cols[$i]).trim()
}

# string Substring(int startIndex, int length)                                                                                         

Get-Content $colsfile | select -skip 1 | ForEach {
  $hash = [ordered]@{}
  for ($i = 0; $i -lt $headers.length; $i++) {
    $value = $_.substring($cols[$i], $cols[$i+1]-$cols[$i]).trim()
    $hash += @{$headers[$i] = $value}
  }
  [pscustomobject]$hash
} | export-csv $csvfile

Eshelman

Answer 3

Here is the working code. Fixed few bugs.

CD 'C:\\FOLDERPATH\'
$filter = "FILE_NAME_*.txt" 

$columns = 11,22,32,42,54 

# DO NOT NEED TO REVERSE [array]::Reverse($columns) #too lazy to re-write array after finding out I need to iterate in reverse

$files = get-childitem ./ |where-object {$_.Name -like $filter}
$newDelimiter = '|'

foreach($file in $files)
{
    $file

    $csvFile = 'C:\\FOLDERPATH\NEW_' + $file.BaseName + '.txt'
    if (!(get-childitem ./ |where-object {$_.Name -like $csvFile})) #check whether file has been processed
    { 

        $content | ForEach {
            $line = $_
            $counter = 0
            $columns | ForEach {
                $line = $line.Insert($_+$counter, $newDelimiter)  
                $counter = $counter + 1
                }
            $line = $line.Trim($newDelimiter)
            $line
        } | set-content $csvFile
    }

} 

Anticipate

When I try to use this script it gives me the following error. Do you know why: Mode LastWriteTime Length Name ---- ------------- ------ ---- -a--- 20/01/2022 08:49 52390 test.ADP InvalidOperation: untitled:Untitled-1:23:17 Line | 23 | $line = $line.Insert($_+$counter, $newDelimiter) | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | You cannot call a method on a null-valued expression. – Gobo