Snippet Name: CSV_to_Excel_Conversion

Tags:

Created Date: 2025-06-16 13:12:43
Last Modified Date: 2025-05-31 10:32:02

Dependencies:

DBA Notes:

Sample Output:

Title: CSV to Excel Conversion
Author: user
i have a folder full of csv files. i would llike to convert each file to an excel file, with colros, headers and filters

Author: ChatGPT
You can achieve this using PowerShell and the `EPPlus` library to create Excel files with formatting. Here's how you can do it:

### Steps:

1. **Install EPPlus**: EPPlus is a .NET library for working with Excel files. Install it using PowerShell:
   ```powershell
   Install-Package EPPlus
   ```

2. **Script to Convert CSV to Excel**: Use the following PowerShell script to read each CSV file, create an Excel file, and apply formatting:

   ```powershell
   # Load the EPPlus library
   Add-Type -Path "C:\Path\To\EPPlus.dll"

   # Input and output directories
   $inputFolder = "C:\Path\To\CSVFiles"
   $outputFolder = "C:\Path\To\ExcelFiles"

   # Ensure the output folder exists
   if (-not (Test-Path -Path $outputFolder)) {
       New-Item -Path $outputFolder -ItemType Directory
   }

   # Process each CSV file
   Get-ChildItem -Path $inputFolder -Filter *.csv | ForEach-Object {
       $csvFile = $_.FullName
       $excelFile = Join-Path -Path $outputFolder -ChildPath ($_.BaseName + ".xlsx")

       # Read the CSV file into an array
       $csvData = Import-Csv -Path $csvFile

       # Create a new Excel package
       $excel = New-Object OfficeOpenXml.ExcelPackage
       $worksheet = $excel.Workbook.Worksheets.Add("Sheet1")

       # Add headers and data
       $columns = $csvData[0].PSObject.Properties | Select-Object -ExpandProperty Name
       $row = 1
       $col = 1

       # Add headers with formatting
       foreach ($header in $columns) {
           $cell = $worksheet.Cells[$row, $col]
           $cell.Value = $header
           $cell.Style.Font.Bold = $true
           $cell.Style.Fill.PatternType = [OfficeOpenXml.Style.ExcelFillStyle]::Solid
           $cell.Style.Fill.BackgroundColor.SetColor([System.Drawing.Color]::LightBlue)
           $col++
       }

       # Add data rows
       foreach ($record in $csvData) {
           $row++
           $col = 1
           foreach ($field in $columns) {
               $worksheet.Cells[$row, $col].Value = $record.$field
               $col++
           }
       }

       # Auto-fit columns and apply filters
       $worksheet.Cells.AutoFitColumns()
       $worksheet.Cells[1, 1, $row, $columns.Count].AutoFilter = $true

       # Save the Excel file
       $excel.SaveAs($excelFile)
       $excel.Dispose()

       Write-Host "Converted $($_.Name) to Excel"
   }

   Write-Host "Conversion complete. Files saved to $outputFolder."
   ```

### Key Features of the Script:
- **Header Formatting**: Headers are bold and have a light blue background.
- **Auto-Fit Columns**: Columns are auto-sized to fit the content.
- **Filters**: Auto-filters are applied to the header row.
- **Dynamic Processing**: Automatically processes all CSV files in the specified folder.

### Example Folder Structure:
- Input Folder: `C:\Path\To\CSVFiles`
  - `file1.csv`
  - `file2.csv`
- Output Folder: `C:\Path\To\ExcelFiles`
  - `file1.xlsx`
  - `file2.xlsx`

Replace the paths and run the script in PowerShell. The output Excel files will have headers, filters, and basic formatting applied. Let me know if you need further customization!