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

**URL:** <https://forums.powershell.org/t/creating-a-script-to-convert-excel-to-csv-with-additional-goals/12109>\
**Category:** PowerShell Help\
**Created:** [February 28, 2019, 10:40am UTC](https://forums.powershell.org/t/creating-a-script-to-convert-excel-to-csv-with-additional-goals/12109 "2019-02-28T10:40:23Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![digitus](https://avatars.discourse-cdn.com/v4/letter/d/f9ae1b/32.png) [@digitus](https://forums.powershell.org/u/digitus)\
**Post date:** [February 28, 2019, 10:40am UTC](https://forums.powershell.org/t/creating-a-script-to-convert-excel-to-csv-with-additional-goals/12109/1 "2019-02-28T10:40:23Z")

</div>

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]

---

<div class="post-metadata">

**Author:** ![digitus](https://avatars.discourse-cdn.com/v4/letter/d/f9ae1b/32.png) [@digitus](https://forums.powershell.org/u/digitus)\
**Post date:** [February 28, 2019, 11:30am UTC](https://forums.powershell.org/t/creating-a-script-to-convert-excel-to-csv-with-additional-goals/12109/2 "2019-02-28T11:30:08Z")

</div>

Updated the code to contain my latest iteration.

---

<div class="post-metadata">

**Author:** ![kvprasoon](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/kvprasoon/32/4449_2.png) [@kvprasoon](https://forums.powershell.org/u/kvprasoon)\
**Post date:** [February 28, 2019, 1:40pm UTC](https://forums.powershell.org/t/creating-a-script-to-convert-excel-to-csv-with-additional-goals/12109/3 "2019-02-28T13:40:41Z")

</div>

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

> **[ImportExcel 5.4.4](https://www.powershellgallery.com/packages/ImportExcel/5.4.4)**
>
> PowerShell module to import/export Excel spreadsheets, without Excel.
> Check out the How To Videos https://www.youtube.com/watch?v=U3Ne\_yX4tYo&list=PL5uoqS92stXioZw-u-ze\_NtvSo0k0K0kq

---

<div class="post-metadata">

**Author:** ![digitus](https://avatars.discourse-cdn.com/v4/letter/d/f9ae1b/32.png) [@digitus](https://forums.powershell.org/u/digitus)\
**Post date:** [March 1, 2019, 9:14am UTC](https://forums.powershell.org/t/creating-a-script-to-convert-excel-to-csv-with-additional-goals/12109/4 "2019-03-01T09:14:49Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [March 1, 2019, 9:51am UTC](https://forums.powershell.org/t/creating-a-script-to-convert-excel-to-csv-with-additional-goals/12109/5 "2019-03-01T09:51:12Z")

</div>

> [@](#):
>
> Unfortunately the goal is to avoid the use of modules

Just out of curiosity, why?

> [@](#):
>
> and have few dependencies so the script can be automated.

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? 😉 😃 At least try it.

---

<div class="post-metadata">

**Author:** ![digitus](https://avatars.discourse-cdn.com/v4/letter/d/f9ae1b/32.png) [@digitus](https://forums.powershell.org/u/digitus)\
**Post date:** [March 1, 2019, 2:43pm UTC](https://forums.powershell.org/t/creating-a-script-to-convert-excel-to-csv-with-additional-goals/12109/6 "2019-03-01T14:43:15Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![cschau2](https://avatars.discourse-cdn.com/v4/letter/c/a9a28c/32.png) [@cschau2](https://forums.powershell.org/u/cschau2)\
**Post date:** [May 31, 2019, 11:30am UTC](https://forums.powershell.org/t/creating-a-script-to-convert-excel-to-csv-with-additional-goals/12109/7 "2019-05-31T11:30:56Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![dotnVo](https://avatars.discourse-cdn.com/v4/letter/d/4af34b/32.png) [@dotnVo](https://forums.powershell.org/u/dotnVo)\
**Post date:** [May 16, 2024, 8:32pm UTC](https://forums.powershell.org/t/creating-a-script-to-convert-excel-to-csv-with-additional-goals/12109/8 "2024-05-16T20:32:42Z")

</div>