CODEWITHSUNDEEP
stringpowershellcsv
ThatGuy
ThatGuy

Reputation: 13

Powershell: Splitting String into CSV

I have a (hopefully) quick question that I can't seem to work out on my own.

I have a string:

ITEM1 Quantity: 12x 355ml bottlePrice: $23.95 $23.50 SAVE $0.45

That I would like to split out and insert into a CSV file. The columns in the generated CSV, and their values, would be:

Name: (everything before "Quantity:" in the above string.)

Quantity: (between Quantity and x)

Size: (Everything between x and bottle)

OrigPrice: (Everything after Price and the second $ sign)

SalePrice: (Everything between OrigPrice and SAVE)

Savings: (Everything after "SAVE")

I hope this all makes sense, I can provide more info if needed.

I appreciate the help!

Upvotes: 1

Views: 991

Answers (1)

Hunter Eidson
Hunter Eidson

Reputation: 1909

How about something like:

$subject = 'ITEM1 Quantity: 12x 355ml bottlePrice: $23.95 $23.50 SAVE $0.45'

if ($subject -cmatch '^(?<Name>.*)Quantity:(?<Quantity>.*)x(?<Size>.*)bottle\s*Price:(?<OrigPrice>.*)\s*(?<SalePrice>\$.*)SAVE(?<Savings>.*)$') {
    $result = $matches[0]
} else {
    $result = ''
}

"Matches:"
$matches

I couldn't tell if there really needed to be a space between bottle & Price (it didn't look like it, but it'll handle it if there is).

If you need the name, you can access it like:

$matches["Name"]

A better solution (and one that actually gets it to CSV format, would be something like the following (thanks to @nickptrvc for pointing out what I missed):

function Read-Data {
[cmdletbinding()]
    param(
        [parameter(Mandatory)][string]$Path
    )

    $reader = [System.IO.File]::OpenText($Path)

    while(( $subject = $reader.ReadLine()) -ne $null ) {
        if ($subject -cmatch '^(?<Name>.*)Quantity:(?<Quantity>.*)x(?<Size>.*)bottle\s*Price:(?<OrigPrice>.*)\s*(?<SalePrice>\$.*)SAVE(?<Savings>.*)$') {
            $result = $matches[0]
            $obj = [PSCustomObject]@{
                Name=$matches["Name"].trim();
                Quantity=$matches["Quantity"].trim();
                Size=$matches["Size"].trim();
                OrigPrice=$matches["OrigPrice"].Trim();
                SalePrice=$matches["SalePrice"].Trim();
                Savings=$matches["Savings"].Trim()
            }
            $obj
        }
    }
}

Then, to use it, save this to a file (I called mine Read-Data.ps1), source the file, and then you have two options: 1) you can use ConvertTo-Csv to simply convert the objects to CSV, and return the result to the screen, or you can use Export-Csv to save it to a file:

. C:\Test\Convert-Data.ps1
Read-Data -Path C:\Test\datafile.dat | ConvertTo-Csv -NoTypeInformation

or

Read-Data -Path C:\Test\datafile.dat | Export-Csv -NoTypeInformation -Path C:\Test\datafile.csv

Upvotes: 2

Related Questions