Converting .xlsx to .csv

PowerShell Help
1

I am have a problem converting my multisheet .xlsx files to .csv where it will create a .csv file for everysheet on the .xlsx file. I need to poll a folder and when a new .xlsx file is dropped into the folder then it converts the file and saves it to another folder and moves the original file to an archive folder. the console is telling me that it is converting the file but it is not creating the converted files, would someone be able to help me.

$ErrorActionPreference = "Stop"


$inputFolder = "C:\PYTHON\input_folder"
$outputFolder = "C:\PYTHON\output_folder"


Import-Module ImportExcel


function Convert-ExcelToCsv {
    param (
        [string]$excelFilePath,
        [string]$outputFolder
    )

    try {
     
        $excelData = Import-Excel -Path $excelFilePath

  
        foreach ($worksheet in $excelData.Workbook.Worksheets) {
            $csvFileName = "$($worksheet.Name).csv"
            $csvPath = Join-Path $outputFolder $csvFileName
            Write-Host "Creating CSV: $csvPath"

            $csvData = $worksheet.Cells.Text -split '\r?\n' | ConvertFrom-Csv
            $csvData | Export-Csv -Path $csvPath -NoTypeInformation -Force
        }

        Write-Host "Converted $excelFilePath to CSV"
    } catch {
        Write-Host "Error converting $($excelFilePath): $($_.Exception.Message)"
    }
}






while ($true) {
    $latestFile = Get-ChildItem -Path $inputFolder -Filter *.xlsx | Sort-Object LastWriteTime -Descending | Select-Object -First 1

    if ($latestFile -ne $null) {
        Write-Host "New file detected: $($latestFile.FullName)"

    
        Convert-ExcelToCsv -excelFilePath $latestFile.FullName -outputFolder $outputFolder


        $archiveFolder = Join-Path $inputFolder "Archive"
        if (-not (Test-Path $archiveFolder)) {
            New-Item -ItemType Directory -Path $archiveFolder | Out-Null
        }
        Move-Item -Path $latestFile.FullName -Destination $archiveFolder

      
        Start-Sleep -Seconds 5

     
        

        Get-ChildItem -Path $outputFolder
    }

    # Sleep for a short duration before checking again
    Start-Sleep -Seconds 2
}

I am getting the following output on the console
New file detected: C:\PYTHON\input_folder\carrier_info.XLSX
Converted C:\PYTHON\input_folder\carrier_info.XLSX to CSV

but there is no files in my output folder. does anyone have any ideas why this is not working or any ideas how I achieve my goal of converting the files into my output folder.

thanks,

2

Derek,
Welcome to the forum. :wave:t3:

What version of the ImportExcel module do you use?

Did you write this code yourself? Have you tried to validate (debug) the individual commands in your function?
I’d start with one *.XLSX file and run each individual command and inspect the output.

Let’s start with reading your XLSX file with the command

$excelData = Import-Excel -Path 'Path\to\your\sampleExcelFile.xlsx'

Now - according to your code there should be a list of WorkSheets from a WorkBook. Let’s run

$excelData.Workbook.Worksheets

What do you get? Or let’s do one step back …

$excelData.Workbook

What do you get?