August 16, 2023 at 11:00 am
This script converts from xlsx to csv, but how can I change delimiter from comma to semicolon, and pickup the same file name from multiple folders to process?
Thanks.
# To execute the script without agreeing with the execution policy
Set-ExecutionPolicy Bypass -Scope Process
# Defines the directory where the file is located
$dir = "D:\"
# Defines the name of the Excel file
$excelFileName = "YOUR_FILE_NAME"
# Define a function to convert the file
Function ExportWSToCSV ($excelFileName, $csvLoc)
{
$excelFile = $dir + $excelFileName + ".xlsx"
$E = New-Object -ComObject Excel.Application
$E.Visible = $false
$E.DisplayAlerts = $false
$wb = $E.Workbooks.Open($excelFile)
foreach ($ws in $wb.Worksheets)
{
$n = $excelFileName + "_" + $WS.Name
$ws.SaveAs($csvLoc + $n + ".csv", 6)
}
$E.Quit()
}
# For each file in the directory with the xlsx format, convert to CSV using the function above
$ens = Get-ChildItem $dir -filter *.xlsx
foreach($e in $ens)
{
ExportWSToCSV -excelFileName $e.BaseName -csvLoc $dir
}
August 16, 2023 at 12:23 pm
two comments
1 - your script is using Excel - this will not be supported on a Server and will have licensing implications to your company - are you aware of it?
2 - as you are using Excel you should look at Excel documentation to see how the saveas works in terms of delimiter. Plenty of documentation on it.
for the multiple folders - you should know by now, based on some of your other questions and their replies, that the powershell get-childitem has further options - one of them is what you need. so again read the documentation before asking questions that you could have found out yourself easily.
August 16, 2023 at 12:55 pm
Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply