Import csv to excel using powershell

Question

I'm trying to import a .txt file which is comma-separated. It gets imported. But excel doesn't seem to understand that it is, comma-separated. It displays all in the same column.

[threading.thread]::CurrentThread.CurrentCulture = 'en-US'
$wbpath=Join-Path "$psscriptroot" 'file.xlsx'
$importcsv=Join-Path "$psscriptroot" 'file.txt'
$xl = New-Object -ComObject Excel.Application
$xl.Visible = $false
$xl.Workbooks.OpenText($importcsv)
$xl.DisplayAlerts = $false
[threading.thread]::CurrentThread.CurrentCulture = 'en-US'
$xl.ActiveWorkbook.SaveAs($wbpath,51)
$xl.Quit()

Any suggestions? Thanks.

MatthewG · Accepted Answer · 2015-02-12 20:32:25Z

3

You have two options. One, if your file extension is .csv instead of .txt, it would work as is. The second option, make sure you pass $True for the comma-delimiter parameter, like so:

$xl.Workbooks.OpenText($importcsv, 2, 1, 1, 1, $False, $False, $False, $True)

answered Feb 12, 2015 at 20:32

MatthewG

9,4032 gold badges28 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3019059 Over a year ago

Thank you. All solutions worked. I choose to just rename the file to .csv.

TheMadTechnician · Accepted Answer · 2015-02-12 20:32:12Z

You need to supply more parameters to the OpenText method to get it to see the delimiter.

$wbpath=Join-Path "$psscriptroot" 'file.xlsx'
$importcsv=Join-Path "$psscriptroot" 'file.txt'
$xl = New-Object -ComObject Excel.Application
$xl.Visible = $true
$xlWindows=2
$xlDelimited=1
$xlTextQualifierDoubleQuote=1
$StartRow=1
$xl.workbooks.OpenText($importcsv,$xlWindows,$StartRow,$xlDelimited,$xlTextQualifierDoubleQuote,$false,$false,$false,$true)
$xl.ActiveWorkbook.SaveAs($wbpath,51)
$xl.Quit()

See the MSDN reference for full details:
https://msdn.microsoft.com/en-us/library/office/ff837097.aspx
The first $false tells it not to consider consecutive delimiters as one, the next one tells it not to consider Tab as a delimiter, the next $false does the same for semicolon, and the $true tells it to use comma as a delimiter. There are additional delimiter options after that that I have not included as the delimiters are all optional, and you only have to include parameters up to the last relevant one to your needs (setting comma to $true).

Collectives™ on Stack Overflow

Import csv to excel using powershell

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related