Submitted By: Kent Finkle
Parses comma-separated values in a spreadsheet column into multiple columns (for example, the value Olympia, WA becomes two separate values: Olympia and WA).
$comments = @' Script name: Convert-TextToColumns.ps1 Created on: Sunday, July 08, 2007 Author: Kent Finkle Purpose: How can I use Windows Powershell to Use the Text to Columns Feature in Microsoft Excel? '@ #----------------------------------------------------- function Release-Ref ($ref) { ([System.Runtime.InteropServices.Marshal]::ReleaseComObject( [System.__ComObject]$ref) -gt 0) [System.GC]::Collect() [System.GC]::WaitForPendingFinalizers() } #----------------------------------------------------- $xlDelimited = 1 $xlTextQualifierNone = -4142 $xl = new-object -comobject excel.application $xl.Visible = $True $wb = $xl.Workbooks.Add() $ws = $wb.Worksheets.Item(1) $ws.Cells.Item(1, 1).Value() = "Olympia, WA" $ws.Cells.Item(2, 1).Value() = "Salem, OR" $ws.Cells.Item(3, 1).Value() = "Boise, ID" $ws.Cells.Item(4, 1).Value() = "Sacramento, CA" $r = $ws.Range("A1").EntireColumn $r2 = $ws.Range("B1") $a = $r.TextToColumns( ` $r2,$xlDelimited,$xlTextQualifierNone,$False,$False,$False,$True) $a = Release-Ref($r2) $a = Release-Ref($r) $a = Release-Ref($ws) $a = Release-Ref($wb) $a = Release-Ref($xl)
$comments = @' Script name: Convert-TextToColumns.ps1 Created on: Sunday, July 08, 2007 Author: Kent Finkle Purpose: How can I use Windows Powershell to Use the Text to Columns Feature in Microsoft Excel? '@ #----------------------------------------------------- function Release-Ref ($ref) { ([System.Runtime.InteropServices.Marshal]::ReleaseComObject( [System.__ComObject]$ref) -gt 0) [System.GC]::Collect() [System.GC]::WaitForPendingFinalizers() } #----------------------------------------------------- $xlDelimited = 1 $xlTextQualifierNone = -4142 $xl = new-object -comobject excel.application $xl.Visible = $True $wb = $xl.Workbooks.Add() $ws = $wb.Worksheets.Item(1) $ws.Cells.Item(1, 1).Value() = "Olympia, WA" $ws.Cells.Item(2, 1).Value() = "Salem, OR" $ws.Cells.Item(3, 1).Value() = "Boise, ID" $ws.Cells.Item(4, 1).Value() = "Sacramento, CA" $r = $ws.Range("A1").EntireColumn $r2 = $ws.Range("B1") $a = $r.TextToColumns( ` $r2,$xlDelimited,$xlTextQualifierNone,$False,$False,$False,$True) $a = Release-Ref($r2) $a = Release-Ref($r) $a = Release-Ref($ws) $a = Release-Ref($wb) $a = Release-Ref($xl)