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?
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"