将固定宽度的txt文件转换为CSV/set-content或out-file-append吗? [英] Convert fixed width txt file to CSV / set-content or out-file -append?

问题描述

输入文件是固定宽度的txt文件.我的客户通常在Excel中打开它，并手动指定分行符.我希望用逗号替换某些空格，以便我可以解析为CSV并另存为XLS或其他格式.

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

那么写出此内容的最有效方法是什么?我曾希望使用set-content，但是由于我们正在逐行处理，所以我认为这是不可能的，所以我想我要么必须为set-content构建一行行，要么使用write-为每次迭代追加.有没有更有效的方法可以做到这一点?

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应该可以很好地进行一些小的调整.这是一个示例，它如何工作(这是外部foreach循环中的所有内容):

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
    }

顺便说一句，您无需使用'.'分割文件名，而只需使用$file.BaseName即可获得不带扩展名的名称:

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"

这篇关于将固定宽度的txt文件转换为CSV/set-content或out-file-append吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！