Multiple csv files into a xlsx file but different sheets using powershell

Question

I have 20 csv files. Each are unrelated. How do I combine them together into one xlsx file with 20 sheets, each named after the csv files.

$root = "C:\Users\abc\Desktop\testcsv"
$CSVfiles = Get-ChildItem -Path $root -Filter *.csv

$xlsx = "C:\Users\abc\Desktop\testxl.xlsx" #output location
$delimiter = "," #delimiter

#Create a excel
$xl=New-Object -ComObject Excel.Application
$xl.Visible=$true
#add a workbook
$wb=$xl.WorkBooks.add(1)

ForEach ($csv in $CSVfiles){

    #name  the worksheet
    $ws=$wb.WorkSheets.item(1)
    $ws.Name = [io.path]::GetFileNameWithoutExtension($csv)

    $TxtConnector = ("TEXT;" + $csv)
    $Connector = $ws.QueryTables.add($TxtConnector,$ws.Range("A1"))
    $query = $ws.QueryTables.item($Connector.name)
    $query.TextFileOtherDelimiter = $delimiter
    $query.TextFileParseType  = 1
    $query.TextFileColumnDataTypes = ,1 * $ws.Cells.Columns.Count
    $query.AdjustColumnWidth = 1

    # Execute & delete the import query
    $query.Refresh()
    $query.Delete()
    $wb.SaveAs($xlsx,51)
}
# Save & close the Workbook as XLSX.
$xl.Quit()

stackoverflow is not a script writing service. What have you written so far? — EBGreen
– EBGreen, Commented Mar 16, 2018 at 15:42
MS does not provide good APIs for creating office documents via software other than actually running Office (the official interop APIs actually run full Office apps in the background). What you might try is SpreadsheetML, which is a fairly simple xml schema Excel understands and allows for multiple sheets in a single document. — Joel Coehoorn
– Joel Coehoorn, Commented Mar 16, 2018 at 15:44
You want to use ImportExcel module, it does not use com methods and does not require office installation. github.com/dfinke/ImportExcel here's the link — Tomek
– Tomek, Commented Mar 16, 2018 at 16:33

Francesco Mantovani · Accepted Answer · 2018-06-29 03:56:23Z

4

This way, change the first line to the folder where you store those 20 CSV files and then

$path="c:\path\to\folder" #target folder
cd $path;

$csvs = Get-ChildItem .\* -Include *.csv
$y=$csvs.Count
Write-Host "Detected the following CSV files: ($y)"
foreach ($csv in $csvs)
{
Write-Host " "$csv.Name
}
$outputfilename = $(get-date -f yyyyMMdd) + "_" + $env:USERNAME + "_combined-data.xlsx" #creates file name with date/username
Write-Host Creating: $outputfilename
$excelapp = new-object -comobject Excel.Application
$excelapp.sheetsInNewWorkbook = $csvs.Count
$xlsx = $excelapp.Workbooks.Add()
$sheet=1

foreach ($csv in $csvs)
{
$row=1
$column=1
$worksheet = $xlsx.Worksheets.Item($sheet)
$worksheet.Name = $csv.Name
$file = (Get-Content $csv)
foreach($line in $file)
{
$linecontents=$line -split ',(?!\s*\w+")'
foreach($cell in $linecontents)
{
$worksheet.Cells.Item($row,$column) = $cell
$column++
}
$column=1
$row++
}
$sheet++
}
$output = $path + "\" + $outputfilename
$xlsx.SaveAs($output)
$excelapp.quit()
cd \ #returns to drive root

answered Jun 29, 2018 at 3:56

Francesco Mantovani

12.8k19 gold badges104 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

m.zhelieznov Over a year ago

Thank you Francesco, this solution works for me. I have a trouble with the regex rules to split xml. E.g. for now I see two cases when it goes wrong, coma in next text is treated as a separator: char,..." "IDE builds every time, even I'm not the expert with regex, however Excel opens it perfectly (I mean xml), while the script adds some extra cells. Does anybody have a solution in the pocket?

Francesco Mantovani Over a year ago

@m.zhelieznov, post your question in detail, but not here, in a new post. Paste your or my code and explain where you are blocked. Tag the right technology and someone will help you

m.zhelieznov Over a year ago

I got the solution from here and updated your script a bit stackoverflow.com/a/40672956/3516022 :<br/>

#Good regex, but leaves some comas as cells<br/>  $linecontents=$line -split '(\s*"[^"]+"\s*|\s*[^,]+|,)(?=,|$)' foreach($cell in $linecontents) #Don't add cells with coma if ($cell -ne ",") { $worksheet.Cells.Item($row,$column) = $cell $column++

Paul Barton · Accepted Answer · 2020-04-27 11:39:57Z

https://stackoverflow.com/a/51094040/5995160 answer is too slow when dealing with csv's with a ton of data, I modified this solution to use https://github.com/dfinke/ImportExcel. This has greatly improved the performance of this task, at least for me.

Install-Module ImportExcel -scope CurrentUser
$csvs = Get-ChildItem .\* -Include *.csv
$csvCount = $csvs.Count
Write-Host "Detected the following CSV files: ($csvCount)"
foreach ($csv in $csvs) {
    Write-Host " -"$csv.Name
}

$excelFileName = $(get-date -f yyyyMMdd) + "_" + $env:USERNAME + "_combined-data.xlsx"
Write-Host "Creating: $excelFileName"

foreach ($csv in $csvs) {
    $csvPath = ".\" + $csv.Name
    $worksheetName = $csv.Name.Replace(".csv","")
    Write-Host " - Adding $worksheetName to $excelFileName"
    Import-Csv -Path $csvPath | Export-Excel -Path $excelFileName -WorkSheetname $worksheetName
}

This solution assumes that the user has already changed directories to where all the csv's live.

G42 · Accepted Answer · 2018-07-08 20:54:16Z

See below for a solution with uses the OpenText method.

At least two things to note:

I'm assuming your workbook creates a single sheet by default. if creates more than that, you will need to modify the script so that these additional sheets are deleted from the end result.
The way you specify TextFileColumnDataTypes is quite clever. You will need to modify it and feed the array to the FieldInfo argument below. See the documentation linked above for the kind of array it is expecting.

$CSVfiles = Get-ChildItem -Path $root -Filter *.csv

$xlsx = "C:\Users\abc\Desktop\testxl.xlsx" #output location

#Create a excel
$xl = New-Object -ComObject Excel.Application

$xl.Visible=$true

#add a workbook
$wb = $xl.WorkBooks.add(1)

# how many worksheets do you have in your original workbook? Assuming one:
$ws = $wb.Worksheets.Item(1)

ForEach ($csv in $CSVfiles){

    # OpenText method does not work well with csv files
    Copy-Item -Path $csv.FullName -Destination ($csv.FullName).Replace(".csv",".txt") -Force

    # Use OpenText method. FieldInfo will need to be amended to suit your needs
    $xl.WorkBooks.OpenText(`
                    ($file.FullName).Replace(".csv",".txt"),    # Filename
                    2,                 # Origin
                    1,                 # StartRow
                    1,                 # DataType
                    1,                 # TextQualifier
                    $false,            # ConsecutiveDelimiter
                    $false,            # Tab
                    $false,            # Semicolon
                    $true,             # Comma
                    $false,            # Space
                    $false,            # Other
                    $false,            # OtherChar
                    @()                # FieldInfo
    )

    $tempBook   = $xl.ActiveWorkbook

    $tempBook.worksheets.Item(1).Range("A1").Select()         | Out-Null
    $tempBook.worksheets.Item(1).Move($wb.Worksheets.Item(1)) | Out-Null

    # name  the worksheet
    $xl.ActiveSheet.Name = $csv.BaseName

    Remove-Item -Path ($csv.FullName).Replace(".csv",".txt")  -Force

}

$ws.Delete()

# Save & close the Workbook as XLSX.
$wb.SaveAs($xlsx,51)
$wb.Close()

$xl.Quit()

Collectives™ on Stack Overflow

Multiple csv files into a xlsx file but different sheets using powershell

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related