Import data from CSV into Excel with PowerShell

Hello. In one of my previous posts, COMPARE TWO CSV FILES VIA POWERSHELL, I was comparing values from two CSV files, and saved output into CSV again. This is not bad, if you don’t care about formatting or coloring the cells etc.

But what if you would need to do such a thing. What if you want to sort out per date for example, and then color the cells or text. That is why you need Excel.

Basically, we will create a new Excel COMObject, and place all the values from CSV file into Excel file and save it. So, lets start.

First, let’s define path where we have CSV files, the output file, and also, which files we want to import.

$XLOutput= "X:\Study Materials\SCRIPTS\get-report\Get-Report\For-Site\Final_Output.xlsx" ## Output file name
$csvFiles = Get-ChildItem ("$CSVPath\*") -Include *.csv

Next, we should create a new Excel workbook, and make it visible.

$Excel = New-Object -ComObject excel.application
$Excel.visible = $True
$workbooks = $excel.Workbooks.Add()
$CSVSheet = 1

ENABLE DISK CLEANUP ON WINDOWS SERVER 2008 R2

 

Now, we will give a name for our first sheet (I have chosen a name of my CSV file)

$worksheets = $workbooks.worksheets
$CSVFullPath = $CSV.FullName
$SheetName = ($CSV.name -split "\.")[0]
$worksheet = $worksheets.Item($CSVSheet)
$worksheet.Name = $SheetName

Next few lines are basically importing of CSV files, defining from which column and row we select values, what is the end row and column field (Don’t worry if you find ‘ConvertTo-MultiArray’ too confusing, this is a function that I have wrote to concatenate fields if some cell has more entries than one, and separate by Enter key).

$CsvContents = Import-Csv $CSVFullPath
$MultiArray = (ConvertTo-MultiArray $CsvContents -Headers).Value
$StartRowNum = 1
$StartColumnNum = 1
$EndRowNum = $CsvContents.Count + 1
$EndColumnNum = ($CsvContents | Get-Member | Where-Object { $_.MemberType -eq 'NoteProperty' }).Count
$Range = $worksheet.Range($worksheet.Cells($StartRowNum, $StartColumnNum), $worksheet.Cells($EndRowNum, $EndColumnNum))
$Range.Value2 = $MultiArray
$worksheet.UsedRange.EntireColumn.AutoFit()
$worksheet.CellFormat.ShrinkToFit

RESET PASSWORD WITH ASU64

 

Next, we will sort out by column F.

$objRange = $worksheet.UsedRange 
$objRange2 = $Excel.Range("F2") 
[void]$objRange.Sort($objRange2,1,$null,$null,1,$null,1,1)

In the end, we will save the Excel file.

$workbooks.SaveAs($XLOutput,51) 
$workbooks.Saved = $true 
$workbooks.Close() 
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbooks) | Out-Null 
$excel.Quit() 
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null 
[System.GC]::Collect() [System.GC]::WaitForPendingFinalizers()

CREATE ACTIVE DIRECTORY GROUP WITH POWERSHELL

 

As I said before, I have used ConvertTo-Multiarray function to concatenate multiple entries from one cell, instead of creating a new row.


function ConvertTo-MultiArray
{
    param (
        $InputObject,
        [switch]$Headers = $false
    )
    begin
    {
        $Objects = @()
        [ref]$Array = [ref]$null
    }
    process
    {
        $Objects += $InputObject
    }
    end
    {
        $Properties = $Objects[0].PSObject.Properties | ForEach-Object{ $_.Name }
        $Array.Value = New-Object 'object[,]' ($Objects.Count + 1), $Properties.Count
        $ColumnNumber = 0
        if ($Headers)
        {
            $Properties | ForEach-Object{
                $Array.Value[0, $ColumnNumber] = $_.ToString()
                $ColumnNumber++
            }
            $RowNumber = 1
        }
        else
        {
            $RowNumber = 0
        }
        $Objects | ForEach-Object{
            $Item = $_
            $ColumnNumber = 0
            $Properties | ForEach-Object{
                if ($Item.($_) -eq $null)
                {
                    $Array.Value[$RowNumber, $ColumnNumber] = ""
                }
                else
                {
                    $Array.Value[$RowNumber, $ColumnNumber] = $Item.($_).ToString()
                }
                $ColumnNumber++
            }
            $RowNumber++
        }
        $Array
    }
}

REMOTELY CONNECT AND LOGOFF USER WITH PSEXEC

 

So, that should be it. I would like to place this creation of Excel document into a function, so it should look like this:


Function Create-ExcelDocument {
Param(
    $CSVPath = ".\Reports", ## Soruce CSV Folder
    $XLOutput= "X:\Study Materials\SCRIPTS\Get-Report\Get-Report\final_report.xlsx" ## Output file name
)

$csvFiles = Get-ChildItem ("$CSVPath\*") -Include *.csv
$Excel = New-Object -ComObject excel.application
$Excel.visible = $True
$Excel.sheetsInNewWorkbook = $csvFiles.Count
$workbooks = $excel.Workbooks.Add()
$CSVSheet = 1

Foreach ($CSV in $Csvfiles) {
$worksheets = $workbooks.worksheets
$CSVFullPath = $CSV.FullName
$SheetName = ($CSV.name -split "\.")[0]
$worksheet = $worksheets.Item($CSVSheet)
$worksheet.Name = $SheetName
$CsvContents = Import-Csv $CSVFullPath
$MultiArray = (ConvertTo-MultiArray $CsvContents -Headers).Value
$StartRowNum = 1
$StartColumnNum = 1
$EndRowNum = $CsvContents.Count + 1
$EndColumnNum = ($CsvContents | Get-Member | Where-Object { $_.MemberType -eq 'NoteProperty' }).Count
$Range = $worksheet.Range($worksheet.Cells($StartRowNum, $StartColumnNum), $worksheet.Cells($EndRowNum, $EndColumnNum))
$Range.Value2 = $MultiArray
$worksheet.UsedRange.EntireColumn.AutoFit()
$worksheet.CellFormat.ShrinkToFit

$objRange = $worksheet.UsedRange
$objRange2 = $Excel.Range("F2")
[void]$objRange.Sort($objRange2,1,$null,$null,1,$null,1,1)

        }
}