Using TextToColumns with Powershell

Question

_{Friday, October 19, 2012 7:03 PM}

I have a script I'm working on that takes a CSV file (create by dsquery/dsget), opens it in excel, resizes the columns, and then saves it as an XLSX file.  The CSV file contains userids, first name, and last name, but they are all in the same column.  I'm trying to integrate TextToColumns to give each heading (samid, first name, last name) their own column, but I keep getting an error that says "TextToColumns Method of Range class failed."  Any help would be greatly appreciated.

Here is a snippet of the code:

$excel=new-object -comobject excel.application

#Open Workbook Object
$workbook=$excel.workbooks.open("PathToTheFileI'mUsing.csv")

#Define Variables
$worksheets=$workbook.worksheets
$sheet=$worksheets.item(1)
$range=$sheet.usedrange

#Convert Text To Columns
$range.texttocolumns("Destination=Range('A:A')", "DataType=xlFixedWidth", "ConsecutiveDelimiters=$true")

#Set Workbook Properties
$excel.visible=$false
$range.entirecolumn.autofit()

#Ignore Prompt for Overwriting existing file
$excel.displayalerts=$false

#Save as XLSX
$workbook.saveas("PathToTheSaveLocation.xlsx",51)
$workbook.saved=$true

All replies (1)

_{Monday, October 22, 2012 2:56 PM ✅Answered}

Hey guys, I've got my script working.  It may not be the prettiest, but here it is.  I've code blocked the changes I made to get it working.  If you have any suggestions on cleaning it up it would be much appreciated :)

$excel.visible=$true
$worksheets=$workbook.worksheets
$sheet=$worksheets.item(1)
$sheet.activate
$range=$sheet.usedrange

$colA=$sheet.range("A1").EntireColumn
$colrange=$sheet.range("A1")
$xlDelimited = 1
$xlTextQualifier = -4142
$xlTextFormat = 2
#Convert Text To Columns
$colA.texttocolumns($colrange,$xlDelimited,$xlTextQualifier,$true,$false,$false,$false,$true)
#AutoFit Columns
$sheet.columns.autofit()

#Ignore Prompt for Overwriting existing file
$excel.displayalerts=$false

#Save as XLSX
$workbook.saveas("K:\it\admin\userinfoxlsx\file.xlsx",51)
$workbook.saved=$true