Convert xlsx to csv – Powershell

While building out an automation process I needed to convert a xlsx file to a csv file for further parsing within powershell. Unfortunately this is not built into powershell, and excel needs to be installed on the system for this to work. Although its not a powershell cmdlet the solution is quite simple:

For the file location I leverage $pwd, this can be altered to what ever is needed but for a test script this is most likely the easiest way to get it running to test. The script will make a csv file from the xlsx specified using the exact same file name.

21 comments… add one
  • Brian Finn Link Reply

    This is just what I was looking for. Thanks!

    • No problem!

      • Jason Link Reply

        I have a strange issue with this function. It works great running it as a stand alone powershell script, but when it runs as a scheduled task it doesn’t work. Any ideas what might be happening?

        • You need it to run as a logged in user most likely. The way this post was written requires a user profile loaded (COM object requires the profile to work). Try logging in and just disconnecting the user and then run the task, ill bet that works. There are better ways to do this now in powershell 5+ that do not require a user profile. Try this: https://www.powershellgallery.com/packages/ImportExcel/5.0.0

          • Jason Link

            I had to associate the DCOM for Excel with the user for the schedule task to run the function. Was a bit confused at first cause running the script from powershell as the user worked just fine. Many thanks!

  • Ichajdek Link Reply

    Hello,

    a bit improvement. It is going to read all files *.xlsc from the specific folder and transfer to csv files with the original name.

    $Directory = “c:\DownLoad\__Miquel\”

    Function ExcelCSV ($File)
    {
    $Excel = New-Object -ComObject Excel.Application
    $Excel.Visible = $false
    $Excel.DisplayAlerts = $false
    $wb = $Excel.Workbooks.Open($File.FullName)
    foreach ($ws in $wb.Worksheets)
    {
    $ws.SaveAs(($Files.FullName -replace ‘.xlsx$’,”) + “.csv”, 6)
    }
    $wb = $Excel.Workbooks.Close()
    $Excel.Quit()

    }

    Foreach ($Files in (Get-ChildItem -Path $Directory -Filter “*.xlsx”))
    {
    ExcelCSV($Files)
    }

    • Absolutely! Thats more or less how I actually use it in production. I modified it a bit to make a working function that someone could test immediately as I hate code that doesn’t work copy paste 🙂

  • Justin Link Reply

    Came across this via a search and wanted to add a few bits if people are copying and pasting the script to allow pipeline and object return. This allows flexibility to use get-item or get-child and pass via pipe on the command-line, while returning the created object (which you could use elsewhere in PS)

    [cmdletbinding()]
    param (
    [Parameter(Mandatory=$False,ValueFromPipeline=$true,Position=0)]
    [ValidateScript({Test-Path $_})]
    [string]$FileName
    )

    Function ExcelCSV ($File)
    {

    $excelfile = Get-Item $file
    $newfile = ($excelfile.FullName -replace ‘.xlsx$’) + “.csv”
    $Excel = New-Object -ComObject Excel.Application
    $Excel.Visible = $false
    $Excel.DisplayAlerts = $false
    $wb = $Excel.Workbooks.Open($excelfile.FullName)
    foreach ($ws in $wb.Worksheets)
    {
    $ws.SaveAs($newfile, 6)
    }
    $Excel.Quit()
    Get-Item $newfile
    }

    ExcelCSV -File “$FileName”

    • Awesome, I like that. I only use this within a script so I never thought to handle it like that, Thanks!

  • Harsh Link Reply

    Hi Guys,

    These codes are very helpful, but I am having some problems running the codes written by Ichajdek and Justin. Is there something I am missing?

    Thanks,

    Harsh

    • What problems are you having? I have not tested their code personally but it looks correct.

  • rome Link Reply

    Hey, thanks for above examples.
    In my case, worksheets are copied and named correctly, but all content is missing in the resulting csv Files. There is no special formatting or filtering in the xlsx files, Excel V. 14.0.7165.5000 32bit.
    Harsh, copy and paste does not deliver riunabale code, e.g. in ‘.xlsx$’,” the double quote is to be changed into two single quotes.

    • Harsh Link Reply

      Thanks for the advice. I abandoned this effort in power shell as I found a simple html app online which does this via a simple gui. Will be glad to share the link if needed.

  • rome Link Reply

    Some additions I just found out (using Ichajdek´s version):
    – resulting .csv files are empty in case the original xls file contains additional empty tabs (meaning more than one spreadsheet in the file). This is true in every excel file manually created with default Excel preference settings, two more empty tabs in the file are automatically created when first saving the file. Troubleshooted this with ExcelInstance and DisplayAlerts switches set to visible. To correct this behaviour, either delete empty tabs in the source file, or better take every tabs name into the filename that is created to save the file, otherwise the first csv containing valid data will be overwritten twice with empty sheets.
    So I changed it like this this:

    [int]$counter=0
    foreach ($ws in $wb.Worksheets) {
    $counter=$counter++
    $ws.SaveAs(($File.FullName -replace ‘.xlsx$’,”) +”_$counter” “.csv”, 6)
    }

    However I have not found a real working version, above code should have a counter value higher than 0 alerady in the first loop, but is 0 and more files are not generated, e.g. 3 tabs in an xlsx file results in one csv file and also, the result is again an empty csv file.
    (Also note the single quotes were changed, as on this website either characters were changed and are not working in powershell or were entered wrong, and $files is changed to $file which is the same variable and content but within the function it is called $file ).

  • rome Link Reply

    Thanks again for your shares of the scripts.
    Maybe these additions help people in the same situation.

    The original version of the script handling more than one spreadsheet in a file is:

    Function ExcelCSV ($File)
    {
    $excelFile = “$pwd\” + $File + “.xlsx”
    $Excel = New-Object -ComObject Excel.Application
    $Excel.Visible = $false
    $Excel.DisplayAlerts = $false
    $wb = $Excel.Workbooks.Open($excelFile)
    foreach ($ws in $wb.Worksheets)
    {
    $ws.SaveAs(“$pwd\”+$File+”_”+($ws.name)+”.csv”,6)
    }
    $Excel.Quit()
    }

    So simply adding “+”_”+($ws.name)” does the trick, every spreadsheet will result in a separate csv file containing filename and spreadsheet name.
    Same solution for Ichaydeks version.

    • Cool thanks! Glad I at least helped point you in the right direction 🙂

  • Jamie Link Reply

    This code works. It saves the file using the message received time and object attachment name, so each attachment should be saved uniquely. Hope this helps any beginner VBAers.

    Public Sub SaveToDisk(itm As Outlook.MailItem)
    Dim objAtt As Outlook.Attachment
    Dim saveFolder As String
    Dim dateFormat

    dateFormat = Format(itm.ReceivedTime, “yyyy-mm-dd”)

    ‘Change this path to the your folder location
    saveFolder = “C:\Documents”
    For Each objAtt In itm.Attachments
    objAtt.SaveAsFile saveFolder & “\” & dateFormat & objAtt.DisplayName
    Set objAtt = Nothing
    Next
    End Sub

    • Ill be real blunt here. VB shouldn’t be used anymore, it really shouldn’t even be considered. It has not been supported since 2007 and any code that’s currently in VB should be moved over to Posh, python, c#, etc.

  • brayden anthony Link Reply

    Incorrect File names? Just use “BatchRename”. Fell better using computer.

  • Ben Pendygraft Link Reply

    Really useful, thanks!

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.