How to open CSV in Excel using Powershell while supplying a list seperator

2
votes

Hi I'm using a simple Powershell script to convert CSV files to XLSX files. However Excel ignores the list seperator and puts all data in the first column.

  • The list seperator is configured correctly (Start > Control Panel > Regional and Language Options -> Additional Settings)
  • Manually opening the files from Windows Explorer works fine.

However, when opening the CSV in Excel using: 

Function Convert-toExcel {
   $xl = new-object -comobject excel.application
   $xl.visible = $true
   $Workbook = $xl.workbooks.OpenText("$csvfile")
   $Worksheets = $Workbooks.worksheets
}

Everything is put into the first column...

Accoriding to Powershell the list seperator is configured correctly:

(Get-Culture).textinfo
ListSeparator  : ,
4
excelpowershellcsv

4 Answers

0
votes

Try adding the DataType argument to the OpenText method. It appears to take magic arguments.

VBA: Workbooks.OpenText filename:="DATA.TXT", dataType:=xlDelimited, tab:=True

I would guess in powershell it accepts a hash, so:

$xl.Workbooks.OpenText(@{Filename = $CSVFile; dataTyype = "xlDelimited", other = $true; otherchar=':' })

However, I've no way to test this currently.

0
votes

The following script works for me. The one change in functionality I made was that I set Excel.visible to false.

Function Export-CSVToXLS {
Param( 
   [String]$CsvFileLocation
  ,[String]$ExcelFilePath
)
If (Test-Path $ExcelFilePath )
{
    Remove-Item -Path $ExcelFilePath
}

$FixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault 
$Excel = New-Object -ComObject excel.application 

$Excel.visible = $false 
$Excel.Workbooks.OpenText($CsvFileLocation)
$Excel.ActiveWorkbook.SaveAs($ExcelFilePath,$FixedFormat) 
$Excel.Quit() 
Remove-Variable -Name Excel 
[gc]::collect() 
[gc]::WaitForPendingFinalizers()
}

Export-CSVToXLS -CsvFileLocation "C:\Temp\CSV.csv" -ExcelFilePath "C:\Temp\XLS.xlsx"

I compiled this based off of information on the following webpages:

http://blogs.technet.com/b/heyscriptingguy/archive/2010/09/09/copy-csv-columns-to-an-excel-spreadsheet-by-using-powershell.aspx

https://social.technet.microsoft.com/Forums/en-US/919459dc-3bce-4242-bf6b-fdf37de9ae18/powershell-will-not-save-excel-file?forum=winserverpowershell

0
votes

Your original code works fine. My guess is your delimiter in excel just isn't a ",". I've seen this go wrong loads of time. The ps culture has nothing to do with it

0
votes

Use `t (Powershell code for the tab character) instead of , (a comma).

Excel defaults at opening to using text to columns import with tab as the separator.