CODEWITHSUNDEEP
excelpowershellcsvdelimiter
Slaine MacRoth
Slaine MacRoth

Reputation: 41

Powershell - Excel SaveAs csv with specified delimiter

Afternoon all,

Is it possible to save a CSV file using Powershell with a different delimiter, in my case "§". I am using the following script to open and change items in an XLSX file and then wish to save as a "§" delimited CSV. The find and replace method does not work in my case ( (Get-Content -Path $CSVfile).Replace(',','§') | Set-Content -Path $CSVfile2) 

$Path = "C:\ScriptRepository\CQC\DataToLoad\"
$FileName = (Get-ChildItem $path).FullName
$FileName2 = (Get-ChildItem $path).Name
$CSVFile = "$Path\$Filename2.csv"

$Excel = New-Object -ComObject Excel.Application -Property @{Visible = 
$false} 
$Excel.displayalerts=$False 
$Workbook = $Excel.Workbooks.Open($FileName) 
$WorkSheet = $WorkBook.Sheets.Item(2) 
$Worksheet.Activate()
$worksheet.columns.item('G').NumberFormat ="m/d/yyyy" 
$Worksheet.Cells.Item(1,3).Value = "Site ID"
$Worksheet.Cells.Item(1,4).Value = "Site Name"

$Worksheet.SaveAs($CSVFile,
[Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSVWindows)

$workbook.Save()
$workbook.Close()
$Excel.Quit()

Upvotes: 0

Views: 12217

Answers (2)

CBooze
CBooze

Reputation: 21

I know this is an older post but here is an option I recently came across: Just update the e:\projects\dss\pse&g.xlsxwith the source location and file as well as the file.csv with the location and file name. Lastly your Worksheet if it is named differently [Sheet1$].

$oleDbConn = New-Object System.Data.OleDb.OleDbConnection
$oleDbCmd = New-Object System.Data.OleDb.OleDbCommand
$oleDbAdapter = New-Object System.Data.OleDb.OleDbDataAdapter
$dataTable = New-Object System.Data.DataTable
$oleDbConn.ConnectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data 
Source=e:\projects\dss\pse&g.xlsx;Extended Properties=Excel 12.0;Persist Security Info=False"
$oleDbConn.Open()
$oleDbCmd.Connection = $OleDbConn
$oleDbCmd.commandtext = “Select * from [Sheet1$]”
$oleDbAdapter.SelectCommand = $OleDbCmd
$ret=$oleDbAdapter.Fill($dataTable)
Write-Host  "Rows returned:$ret" -ForegroundColor green
$dataTable | Export-Csv file.csv -Delimiter ';'
$oleDbConn.Close()

Source

I was using SaveAs(file.csv,6) but couldn't change the delimiter. Also Ishan's resolution works but I wanted something more OOB as this is going to be used within an SSIS package for myself across different systems and this just works. =)

Upvotes: 1

Ishan
Ishan

Reputation: 4369

Running the following command, will let you save the CSV file using the delimiter §

Import-CSV filename.csv | ConvertTo-CSV -NoTypeInformation -Delimiter "§" | Out-File output_filename.csv

You should check out ImportExcel - PowerShell module to import/export Excel spreadsheets, without Excel. It makes working with excel files easier using powershell.

Upvotes: 1

Related Questions