windows 7 - How can I easily split a CSV into two Excel worksheets with PowerShell?

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.