CODEWITHSUNDEEP
powershell
Mike Q
Mike Q

Reputation: 7327

Create CSV from text file and define fields

I would like to take a text document with spaces between the text and create a CSV file using defined fields. I have searched a bit and not found a way to do this yet. I see a lot of examples handling well defined fields delims but not one that is arbitrary as the following.

Input file:

   1001 Mitch, Bucanon                          NO    NO    Baywatch, TV
   1002 SPARE SPARE SPARE                       NO    NO    SPARE
   1003 Sparrow, Jack                           NO    YES   Pirates, carrb 2

Desired output:

"1001","Mitch, Bucanon","NO","NO","Baywatch,TV"
"1002","SPARE SPARE SPARE","NO","NO","SPARE"
"1003","Sparrow, Jack","NO","YES","Pirates, Carrib 2"

Upvotes: 1

Views: 36

Answers (1)

Ctznkane525
Ctznkane525

Reputation: 7465

It's fixed width, so you need the positions. So, here's an example:

$arr = @()
$arr += "1001 Mitch, Bucanon                          NO    NO    Baywatch, TV"
$arr += "1002 SPARE SPARE SPARE                       NO    NO    SPARE"
$arr += "1003 Sparrow, Jack                           NO    YES   Pirates, carrb 2"

foreach($item in $arr)
{
    $code = $item.SubString(0,4);
    $name = $item.SubString(5, 40).Trim();
    $bin1 = $item.SubString(45, 6).Trim();
    $bin2 = $item.SubString(51, 6).Trim();
    $column5 = $item.SubString(57).Trim();
    write-host ($code + ",""" + $name.Trim() + """,""" + $bin1 + """,""" + $bin2 + """,""" + $column5 + """")
}

Upvotes: 2

Related Questions