Converting Multiple CSV's to a single Xlsx

Question

I have a script that creates x amount of csv's I want to convert all the csv's in the folder into one Xlsx file.

I have the basics of cycling through each csv, however it overwirtes after the end of each loop.

I have want to add each csv to a worksheet in the workbook and save it.

but it only displays the last csv it read and doesn't show any other worksheets.

I have sampled the code from the following;

How to export a CSV to Excel using Powershell

As discussed, I have tried the above; no luck.

Function Excel-Write{
$RD = "C:\temp\Servers\*.csv"
$CsvDir = $RD

$csvs = dir -path $CsvDir
$outputxls = ""C:\temp\Servers\Complete.Xlsx"

foreach($iCsv in $csvs){
  $WB = $csvs.Length
    $Excel = New-Object -ComObject excel.application
    #$Excel.displayAlerts = $false
    $workbook = $excel.Workbooks.add(1)
    $Worksheet = $workbook.worksheets.Item(1)
    $Worksheet.name = $Group

    $TxtConnector = ("TEXT;" + $iCsv)
    $Connector = $worksheet.Querytables.add($txtconnector,$worksheet.Range("A1"))
    $query = $Worksheet.QueryTables.item($Connector.name)

    $query.TextfileOtherDelimiter = $Excel.Application.International(5)

    $Query.TextfileParseType =1
    $Query.TextFileColumnDataTypes = ,2 * $worksheet.cells.column.count
    $query.AdjustColumnWidth =1

    $Query.Refresh()
    $Query.Delete()

        $workbook.Save()

}
$Workbook.SaveAs($outputxls,51)
$Excel.quit()
}

Only shows one worksheet and this is the last CSV that has been read, it overwrites other worksheets.

You might take a look at the great module by Doug Finke [ImportExcel](https://www.powershellgallery.com/packages/ImportExcel/5.2.0). That could make your life easier. — Olaf, Jan 22 '19 at 09:00
you can also merge csv's in one file (vs loop) using cmd's copy: cmd /c "copy *.csv out.data" — Mike Twc, Jan 22 '19 at 19:48

score 1 · Accepted Answer · answered Jan 22 '19 at 09:38

I think you need to move your first two lines out of the loop. Also you need to call add instead of $workbook.worksheets.Item(1) -> $workbook.worksheets.add(1)

Function Excel-Write{
$RD = "C:\temp\Servers\*.csv"
$CsvDir = $RD

$csvs = dir -path $CsvDir
$outputxls = ""C:\temp\Servers\Complete.Xlsx"
$Excel = New-Object -ComObject excel.application
$workbook = $excel.Workbooks.add(1)

foreach($iCsv in $csvs){
  $WB = $csvs.Length
    #$Excel.displayAlerts = $false
    $Worksheet = $workbook.worksheets.add(1)
    $Worksheet.name = $Group

    $TxtConnector = ("TEXT;" + $iCsv)
    $Connector = $worksheet.Querytables.add($txtconnector,$worksheet.Range("A1"))
    $query = $Worksheet.QueryTables.item($Connector.name)

    $query.TextfileOtherDelimiter = $Excel.Application.International(5)

    $Query.TextfileParseType =1
    $Query.TextFileColumnDataTypes = ,2 * $worksheet.cells.column.count
    $query.AdjustColumnWidth =1

    $Query.Refresh()
    $Query.Delete()

        $workbook.Save()

}
$Workbook.SaveAs($outputxls,51)
$Excel.quit()
}

As you are creating the excel object again so it overwrites the existing sheet.

Converting Multiple CSV's to a single Xlsx

1 Answers1