How to change delimiter in excel CSV saving using powershell

I had success with the following code with my own data. This uses your COM Object assignment code. I added logic to extract the cells that contain data, add that data to a new custom object on each row iteration, store each custom object in an array, and finally pipe the array into Export-Csv. Your specified delimiter ; is used in the Export-Csv command.

$excel_session               = New-Object -Com Excel.Application
$excel_session.displayAlerts = $false

# I open the file I need to extract the page from
$excel_workbook              = $excel_session.workbooks.open($args[0])

# I load in the page 
$excel_worksheet             = $excel_workbook.worksheets($page_to_extract)

# Get Range of Used Cells in Worksheet
$range = $excel_worksheet.usedrange

# Get First Row Column Text to be Used as Object Properties
$headers = $range.rows.item(1).value2

# Loop through Rows and Columns to Extract Data
# First loop traverses rows
# Second loop traverses columns

$output = for ($i = 2; $i -le $range.rows.count; $i++) {
    $hash = [ordered]@{}
    for ($j = 1; $j -le $range.columns.count; $j++) {
        [void]$hash.Add($headers.GetValue(1,$j),$range.rows.item($i).columns.item($j).Text)
    }
    [pscustomobject]$hash
    }

$output | Export-Csv file.csv -NoType -Delimiter ';'

# Clean Up COM Objects

[void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel_workbook)
[void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel_session)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

The List Separator is a Windows regional setting.

To change it, please see : https://support.office.com/en-us/article/import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b-339e391393ba

Change the separator in all .csv text files In Microsoft Windows, click the Start button, and then click Control Panel.

Open the dialog box for changing Regional and Language settings.

Type a new separator in the List separator box.

Click OK twice.

Note: After you change the list separator character for your computer, all programs use the new character as a list separator. You can change the character back to the default character by following the same procedure.

You should now be able to change the csv character delimiter.

Please note that you'll need to restart your computer to make the change in effect. You can check your current List Separator value in your Powershell session with (Get-Culture).TextInfo.ListSeparator

You can also check this post, which has a lot of screenshot and different other options on how to do so: https://superuser.com/questions/606272/how-to-get-excel-to-interpret-the-comma-as-a-default-delimiter-in-csv-files

My recommendation is to avoid Excel and use the database objects instead. Example:

[CmdletBinding()]
param(
  [Parameter(Position = 0,Mandatory = $true)]
  [ValidateNotNullOrEmpty()]
  $ExcelFileName,

  [Parameter(Position = 1,Mandatory = $true)]
  [ValidateNotNullOrEmpty()]
  $SheetName
)
$queryString = 'SELECT * FROM [{0}$A1:end]' -f $SheetName
$connectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;" +
  "Data Source=$((Get-Item -LiteralPath $ExcelFileName -ErrorAction Stop).FullName);" +
  "Extended Properties=Excel 8.0;")
try {
  $connection = New-Object Data.OleDb.OleDbConnection($connectionString)
  $command = New-Object Data.OleDb.OleDbCommand($queryString)
  $command.Connection = $connection
  $connection.Open()
  $adapter = New-Object Data.OleDb.OleDbDataAdapter($command)
  $dataTable = New-Object Data.DataTable
  [Void] $adapter.Fill($dataTable)
  $dataTable
}
catch [Management.Automation.MethodInvocationException] {
  Write-Error $_
}
finally {
  $connection.Close()
}

If the above script is Import-ExcelSheet.ps1, you could export to a ;-delimited CSV file by running a command such as:

Import-ExcelSheet "C:\Import Files\ExcelFile.xlsx" "Sheet1" |
  Export-Csv C:\Import Files\Test.Csv" --Delimiter ';' -NoTypeInformation

If you have the 32-bit version of Excel installed, you will need to run the above script in the 32-bit version of PowerShell.

If you don't want to license Excel or can't install it on some computer where you want to run the script, you can install the Access database engine instead:

https://www.microsoft.com/en-us/download/details.aspx?id=54920