Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I recently found out that PowerShell can be used to write Excel documents. The first use I thought of for this was to (at least partially) automate parsing of some CSV reports that I regularly have to work with.

The basic job I need to do goes something like this:

  1. Create an Excel workbook with two worksheets.
  2. Copy the column headers from the CSV file to Row 1 on both sheets.
  3. For each row in the CSV that meets a certain condition (e.g.: value in column R equals "Don't put in Sheet 1") append the row to Excel worksheet 2.
  4. Each row in the CSV that is not destined for Excel worksheet 2 should be appended to Excel worksheet 1.

Step 1 is simple enough - I've found several guides online to help me get started there. I'm also confident I can handle the conditional logic required for steps 3 & 4 on my own, based on past PowerShell experience. The problem I'm having a hard time finding a solution for is writing all the data into the sheets.

I haven't actually written any Excel-generating PowerShell scripts just yet, but every guide that I've seen only seems to include instructions for writing individual cells - not entire rows at once. Is there an easier way to do this?

Some things I'd be interested in, if they exist:

  • Some means of copying an entire row from a CSV file to an Excel worksheet without having to loop through individual cells in the row.
  • Some way of copying an entire row from one CSV file to another without having to loop through individual cells, plus a command (or reasonably short script) to import a whole CSV file into an Excel Worksheet.
  • Last resort: A for loop (or similarly short script block) to be used in place of a write-entire-row method. This should be able to determine where to stop copying cells in a row without having to go all the way to the last column (I think it's XFD these days), while gracefully handling empty cells in the middle of a row.

Aside from figuring out the above, the real hard part of my script is going to be making sure it can work efficiently on source CSV files that may be thousands (possibly tens of thousands) of rows long.

The final script will need to be compatible with Windows 7, PowerShell 2.0, and Excel 2010.

share|improve this question

1 Answer 1

Only a partial answer

I know a short script for one of your questions:
[...] plus a method (or reasonably short script) to import a whole CSV file into an Excel Worksheet.

$csv = Get-Item "C:\input.csv"
$excel = New-Object -ComObject excel.application 
$excel.visible = $true
$workbook = $excel.Workbooks.Add()
$worksheet = $workbook.worksheets.Item(1)

$Connector = $worksheet.QueryTables.add("TEXT;" + $csv.fullname,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)
$query.Refresh()

The magic is done at the last 3 lines where you utilize Excel's internal data import method. Same as Excel → Data → Get external data

share|improve this answer
    
Question I've been meaning to ask: Is $excel.visibile = $true really necessary? I've seen it in just about every sample script I've found online but it would seem like it should only be needed if you actually want to see Excel while the script is running - correct? –  Iszi Sep 8 '13 at 16:05
    
Thats correct, you don't need it. The Excel process is running in the background and executes all your commands even when invisible. Maybe you are also interested in some more customization options. Please see the script posted here. –  nixda Sep 8 '13 at 16:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.