Copy CSV Columns to an Excel Spreadsheet by Using PowerShell

re: Copy CSV Columns to an Excel Spreadsheet by Using PowerShell

JB — Thu, 19 Jan 2012 12:24:34 GMT

@jrv: I solved my problem!

Here's the code, where I copy the content of "input.csv" in the "specific_tab" of the "output.xlsx" file. In this example, there is only 2 columns in input.csv (if more are present, change the "A1:B1" portion of this code). Note that excel turns the csv filename into the tab name automatically, make sure that this line "$Worksheet = $Workbook.WorkSheets.item("input")" is adjusted.

$sourcefile = "C:\input.csv"

$updatedfile = "C:\output.xlsx"

$Excel = New-Object -ComObject excel.application

$Excel.visible = $false

$Workbook = $excel.Workbooks.open($sourcefile)

$Worksheet = $Workbook.WorkSheets.item("input")

$worksheet.activate()

$range = $WorkSheet.Range("A1:B1").EntireColumn

$range.Copy() | out-null

$Workbook = $excel.Workbooks.open($updatedfile)

$Worksheet = $Workbook.WorkSheets.item("specific_tab")

$worksheet.activate()

$Range = $Worksheet.Range("A1")

$Worksheet.Paste($range)

$workbook.Save()

$Excel.Quit()

Remove-Variable -Name excel

[gc]::collect()

[gc]::WaitForPendingFinalizers()

re: Copy CSV Columns to an Excel Spreadsheet by Using PowerShell

jrv — Tue, 17 Jan 2012 22:48:42 GMT

@JB

You need to use Excel directly. It is not necessary to import. YOur master sheet can read data directly from a CSV file.

There are a number of places where you can get more help on this.

You can post in an Excel forum at Microsoft:social.technet.microsoft.com/.../threads

You can post at The Scripting Guys Forum at Microsoft: social.technet.microsoft.com/.../ITC

You can post at: www.scriptinganswers.com

Each forum has its own particular strengths and weaknesses.

re: Copy CSV Columns to an Excel Spreadsheet by Using PowerShell

JB — Tue, 17 Jan 2012 21:56:31 GMT

@jrv: the CSV data need to be pushed into a XLSX file where many graphs and pivot tables depend on this data. Note that this process will be automated (unsupervised) and will either do daily, weekly, or monthly data concatenation/handling. I've found another method here: gallery.technet.microsoft.com/.../d41565f1-37ef-43cb-9462-a08cd5a610e2 but my powershell skills are way to low to make it work... when i pipe the csv file it only loads the filename lenght or each line byte count... i'm desperate now... help!

re: Copy CSV Columns to an Excel Spreadsheet by Using PowerShell

jrv — Tue, 17 Jan 2012 19:19:55 GMT

What is not made clear here is that there is no need to import a CSV into an Excel spreadsheet. a CSV file will open directly in Excel and can be easily saved in many formats.

This is very fast.

The only reason to do an import is if you want to edit the values or filter the import. This too may be easier after just opening the CSV in Excel directly.

re: Copy CSV Columns to an Excel Spreadsheet by Using PowerShell

JB — Tue, 17 Jan 2012 18:28:32 GMT

This works for me, but can end up being pretty long. On my server, it takes approx 0.04 seconds per imported cell. Do the math, but with a 10,000 rows spreadsheet with 86 columns, it takes over 9 hours to complete! Any suggestions to make it faster? Maybe something else than a "foreach" loop... ?

re: Copy CSV Columns to an Excel Spreadsheet by Using PowerShell

ScriptingGuy1 — Mon, 20 Sep 2010 20:28:22 GMT

@Matt B. you can use ADO to write to Access, or to SQL. See the ADO tag on this blog, or the Scripting for databases on the Learn to script page.

@WF probably a good thing to do.

@Steven White MSDN is the comprenhensive source of information about everything you can do with Microsoft Excel. See the hyperlinks in the article for guidance.

@WhoEver abstraction is always a good thing, but generally makes the script more complex to understand. Feel free to modify the script and post it to the Repository.

re: Copy CSV Columns to an Excel Spreadsheet by Using PowerShell

Whoever — Sat, 18 Sep 2010 01:34:51 GMT

This post would have been considerably more useful if it was generalized. Everything is hard-coded. Can we not get and print the column names dynamically; deal with an arbitrary number of columns; pass the paths to the script, etc?

re: Copy CSV Columns to an Excel Spreadsheet by Using PowerShell

Steven — Thu, 09 Sep 2010 16:29:52 GMT

Pardon my premature question. The script error was my own fault (no dollar sign before excel.workbooks.add) but the question seems reasonable, namely, is there documentation on all these methods. I do see the examples in the script repository, but that is not the same as a comprehensive list of all the things one can do with/to an excel spreadsheet.

Thank you.

re: Copy CSV Columns to an Excel Spreadsheet by Using PowerShell

Steven White — Thu, 09 Sep 2010 16:07:45 GMT

I tried a variation of this, and on the

$workbook = excel.workbooks.add()

line I got an error about an expression expected after the left parenthesis.

Where is the documentation on these various methods for excel?

Thank you.

re: Copy CSV Columns to an Excel Spreadsheet by Using PowerShell

WF — Thu, 09 Sep 2010 14:20:43 GMT

Just wondered what would be the best way to get rid of the Excel COM object. The Windows PowerShell Tip of the Week "Getting Rid of a COM Object (Once and For All)" at technet.microsoft.com/.../ff730962.aspx recommends: [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)