Importing large csv file into Excel using PowerShell

Question

I'm writing a script which imports a large csv file in Excel document. I try to use a faster way to enter the data and pass the array directly to Excel without looping it.

$p = Import-Csv -Path "C:\Report.csv" -Delimiter "`t"
$Excel01 = New-Object -ComObject Excel.Application 
$Excel01.Visible = $True 
$Workbook01 = $Excel01.Workbooks.Add() 
$Worksheet01 = $Workbook01.Sheets.Item(1) 
$Worksheet01.Activate() 

$Worksheet01.Range("A1:D1").EntireColumn.Value() = $p | select field1,field2...

But when I run this it hungs...How can I do that?

David Brabant, I need query SQL server for data and save it as xlsx file and send it by email (and repeat this stuff several times), that's why I try to automate these actions. So, I use bcp command line tool to export data from mssql to csv file and then I need somehow convert it in the Excel format. — BIB
– BIB, Commented Aug 29, 2014 at 7:31

Ansgar Wiechers · Accepted Answer · 2014-08-29 09:30:06Z

OpenText() already exists in Excel. Note, however, that you MUST change the extension of the text file to something other than .csv, because Excel has its own mind about how files with that particular extension should be handled.

New-Variable -Option Constant -Name xlDelimited -Value 1
New-Variable -Option Constant -Name xlTextQualifierNone -Value -4142
New-Variable -Option Constant -Name xlWorkbookDefault -Value 51

$csv = 'C:\path\to\your.csv'
$txt = $csv -replace '\.csv$','.txt'
$xls = $csv -replace '\.csv$','.xlsx'

Rename-Item $csv $txt

$xl = New-Object -COM 'Excel.Application'
$xl.Workbooks.OpenText($txt, [Type]::Missing, [Type]::Missing, $xlDelimited, $xlTextQualifierNone, $false, $true)
$wb = $xl.Workbooks | ? { $_.FullName -eq $txt }

$wb.SaveAs($xls, $xlWorkbookDefault)
$wb.Close()

$xl.Quit()

The [Type]::Missing values are required for parameters that should retain their default value.

Patrick · Accepted Answer · 2014-08-29 09:21:54Z

Quick and dirty. Maybe you can optimize it :-)

$p = Import-Csv -Path "C:\Report.csv" -Delimiter "`t"
$Excel01 = New-Object -ComObject Excel.Application 
$Excel01.Visible = $True 
$Workbook01 = $Excel01.Workbooks.Add() 
$Worksheet01 = $Workbook01.Sheets.Item(1) 
$Worksheet01.Activate() 

#Add csv header to excel
For ($i = 0; $i -lt ($p | Get-Member | Where-Object -FilterScript {$_.MemberType -eq "NoteProperty"}).Count; $i ++) {
    $Worksheet01.Cells.Item(1,(1+$i)) = "$(($p | Get-Member | Where-Object -FilterScript {$_.MemberType -eq "NoteProperty"})[$i].Name)"
}

#Add csv data to ecxel
$startRow = 2
For ($i = 0; $i -lt ($p | Measure-Object).Count; $i ++) {
    For ($i2 = 0; $i2 -lt ($p[$i] | Get-Member | Where-Object -FilterScript {$_.MemberType -eq "NoteProperty"}).Count; $i2 ++) {
        $PropertyName = ($p[$i2] | Get-Member | Where-Object -FilterScript {$_.MemberType -eq "NoteProperty"})[$i2].Name
        $Worksheet01.Cells.Item($startRow,(1+$i2)) = "$($p[$i].$PropertyName)"
    }
    $startRow ++
}

Collectives™ on Stack Overflow

Importing large csv file into Excel using PowerShell

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related