Creating a Script to Convert Excel to CSV, with additional goals

PowerShell Help
1

Hey guys, I’m looking for some help with my current project for my company. Any resources are welcome, but I’m also open to feedback or input on my code.

I’m a fairly new developer and have just started to learn PowerShell so a lot of this is new to me. If i’m doing things totally wrong, please let me know.

Here’s my code and scope of the project.

[pre]

#BASH SCRIPT
<#
SET dir="%~dp0"
Powershell -Command "\FILELOCATION\XLSX-to-CSV.ps1 Convert-CsvInBatch -folderPath \"dir\""
#>
<#main script
HELP
.Dependencies
Requires EXCEL to be installed on the machine
.Parameters
Script will require a path location for the input files
.Expected Outputs
Script will export all .XLSX files in a given directory or given selected files to .CSV files in "X Directory(s)"
#>
#Modify XLSX Document | Open each EXCEL Object | Save as .CSV File
Function Convert-CsvInBatch
{
[CmdletBinding()]
Param
(
[Parameter(Mandatory=$true)][String]$Folder
)
#Get all .XLSX files in Directory
$ExcelFiles = Get-ChildItem -Path $Folder -Filter *.xlsx -Recurse
#Creates Excel COMObject
$excelApp = New-Object -ComObject Excel.Application
$excelApp.DisplayAlerts = $false
#For Each File, Delete the Top Row
$ExcelFiles | ForEach-Object {
$workbook = $excelApp.Workbooks.Open($_.FullName)
$csvFilePath= $_.FullName-replace"\.xlsx$",".csv"
#DELETE FIRST 2 ROWS IN EXCEL FILE
$p=1
#Count # of Sheets in Workbook
$i=$workbook.Sheets.Count
while ($p-le$i) {
$sheet=$workbook.Sheets.Item($p)
[void]$sheet.Cells.Item(1,1).EntireRow.Delete()
[void]$sheet.Cells.Item(1,1).EntireRow.Delete()
$p++
}
$workbook.SaveAs($csvFilePath, [Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSV)
$workbook.Close()
}
# Release Excel Com Object resource
$excelApp.Workbooks.Close()
$excelApp.Visible = $true
Start-Sleep 5
$excelApp.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelApp) | Out-Null
}
[/pre]
2

Updated the code to contain my latest iteration.

3

Great. Are you facing any problem with this code ? To convert Excel to CSV you can take help of ImportExcel module.

4

Unfortunately the goal is to avoid the use of modules and have few dependencies so the script can be automated. I’ve make some changes to the scope and updates to the code, once I test I’ll post a new update on my process.

Thank you for your input though!

5

Just out of curiosity, why?

It would be ONE dependency and of course you still would be able to automate whatever you want!? I do understand why someone wouldn’t like to have Excel installed only to be able to create an Excel sheet but the module ImportExcel is less than 25 MB … so no big deal at all. And it would make your life much much easier I think. Why inventing the wheel again and again? :wink: :smiley: At least try it.

6

Hey guys, thank you for the help.

I’ve finished the script and gotten it running.

The reason we had to avoid the use of modules was importing them on the organizations systems/servers is difficult due to access restrictions.

Had to change the scope a little for the teams requirements, but it’s my first successful PS endeavour.

Thank you!

7

Hi, is the code above updated to reflect the working version? I have a script that does virtually the same thing, but started generating errors after a February .NET update. Wondering if your solution will work with the updates applied?