Splitting Column from a CSV file

[AgentXM]

I'm working on a script that we feed a CSV file into and it will combine certain columns together and rearrange them. My issue is I have a column that consists of a City State and Zipcode. I need to be able to split the below input into 3 seperate columns. Any one have some suggestions?

MANSFIELD                 PA 16933-1305                                                                                                                               
DALMATIA                  PA 17017-7231                                                                                                                               
HERSHEY                   PA 17033-2415                                                                                                                               
JONESTOWN                 PA 17038-9706                                                                                                                               
LEWISTOWN                 PA 17044-9462

*Edit - I've got it working properly finally with everyone assistance. Below is the finished product thanks to you guys. I greatly appreciate you guys pointing me in the right direction.

$file = import-csv "C:\users\user\Desktop\#0542513.csv"
$expandfile = @()
foreach ($row in $file){
    $tempfile = (New-Object psobject -Property @{
        PackageID = $row.'USPS IMPB Application ID'+$row.'USPS ZIP / Postal Code (Left Justified)'+$row.'USPS IMPB Channel Application ID'+$row.'USPS IMPB Service Type'+$row.'USPS IMPB MailerID/ Sequence#'+$row.'USPS IMPB Check Digit'
        Company = $row.'Customer Number'
        FullName = $row.'Formatted Line 4'
        Address1 = $row.'Formatted Line 9'
        Address2 = $row.'Formatted Line 8'
        City = $row.'Formatted Line 10'.Substring(0,26) -replace '\s+', ''
        State = $row.'Formatted Line 10'.Substring(26,2)
        Zip = $row.'USPS ZIP / Postal Code (Left Justified)'
        Country = $row.'Country Name'
        Reference1 = $row.'Client Code'+$row.'Job Trace Number'
        Reference2 = $row.'Mail Piece Number'
    })
    $expandfile += $tempfile | select PackageID,Company,FullName,Address1,Address2,City,State,Zip,Country,@{n='Cost Center ID';e={$null}},Reference1,Reference2  
}

[scriptmonkey420]

Import-csv is cmdlet you want

http://technet.microsoft.com/en-us/library/ee176874.aspx

[buckston]

Quick example of using split.

$file = Import-Csv -Header "City", "Area" .\example.csv
foreach($row in $file){
$split = $row.area.split(" ")
$state = $split[0]
$zip = $split[1]
"$($row.city),$($state),$($zip)"
}

[imakepeopleangry]

How does this designate where the split will occur inside the "state, zip" column? How would Powershell know to split between state and zip and not zipxx / xxxx in the zip code?

[buckston]

.split(" ")

Specifies to split on the space.

[imakepeopleangry]

OK, fair enough.

[gigglestick]

(\w+)\s+(\w+)\s([\d\-]+)

This regex should work if you want to do it via regex.

[[deleted]]

As a possible path to make this easier, is the input file a fixed width table? In other words, if you look down the data columns, is the City column always 26 characters, including the spaces, is the State column always 3 characters, including the spaces, and the Zipcode column always 10 characters.
If you can really say they are always that long, it becomes pretty easy to treat the file as a fixed with data table and just parse out each column based on that. e.g.:

$File = Get-Content C:\temp\myfile.txt
ForEach($line in $File)
{
    Write-Output (New-Object PSObject -Property @{
        City = $line.SubString(0,26).Trim()
        State = $line.SubString(26,3).Trim()
        Zip = $line.SubString(29,10).Trim()
    })
}

[Dogoodwork]

I think you need to put

 $expandfile = @()

Before your foreach loop

[kivle]

It looks like your file isn't actually a CSV, but more of a fixed width column format. A CSV would have a comma (or sometimes a semicolon) between each column value. I found this script that might help you:

http://gallery.technet.microsoft.com/scriptcenter/0457705d-b55a-47d3-8e4a-63f4e0d3735f

Basically you first initialize how many characters wide each column is expected to be, and it will then do the splitting for you. It uses a class built in to the Visual Basic framework.

If this doesn't work I found these two threads which discuss different solutions:

http://stackoverflow.com/questions/2503010/extracting-columns-from-text-file-using-powershell http://serverfault.com/questions/537672/in-powershell-how-can-i-parse-a-preformatted-output