How to import specific column from all the excel files and export it as a table

PowerShell Help
1

I have several excel file, I just want to copy the particular column from all sheet to new workbook. Below code is not working for me, Any help will be appreciated.

excel_test = New-Object -ComObject Excel.Application

$excel_test.visible =$false
$excel_test.DisplayAlerts =$true

$excelFiles = Get-ChildItem -Path C:\Users\SSampath\Desktop\Test -Include *.xlsx -Recurse

Foreach ($file in $excelFiles)
                        {
                           $workbook = $excel_test.Workbooks.open($file)
                           $Worksheet = $Workbook.WorkSheets.item("Sheet1")
                           $range = $WorkSheet.range("A1:AC1").EntireColumn 
                           $averages=$range.Copy("A1").EntireColumn 
$averages.SaveAs("C:\Users\SSampath\Desktop\Test\Summary.xlsx")
2

Not precisely sure, but this doesn’t look right:

$range = $WorkSheet.range("A1:AC1").EntireColumn

A1:AC1 doesn’t seem like a valid range. In Excel itself, A:A is used to refer to an entire column.

3

Joel - you can of course in Excel have multi-char columns in Excel.

Columns got from A - Z of course, but when you past Z, they start over with A[A-Z].
If you’d exceed that, then it’s B[A-Z]… and so on.

For Example (here is a sum of row A out to 52 columns): ‘=SUM(A1:AZ1)’ the results is 52
This is a left to right thing, not a left to right and top to bottom of each column.

It is normally unusual to see, but some I have seen many from accountants with massive workbooks/spreadsheets.

So, you are correct in saying the range the OP is using is not correct, but it is because the OP is not asking for the column range but the row range.

So, this is really not a PoSH issue, but how the OP needs to call the Excel DOM which the OP needs to work through.

So, the OP must collect all rows and columns.
Example: “=SUM(A1:AZ3)”, which in my sample XLS is 156
Of course that 3*52 (row & columns) in that single row.

See this post.

Read and Get Values from Excel File using PowerShell Script http://mekalikot.blogspot.com/2014/08/read-and-get-values-from-excel-file.html

Or looking at the Excel docs and leverage the Range function from Excel.

More simply the OP could just do this: “=SUM(A:AZ)”
Not adding the Row number, means use the whole column.

One can easily see this by doing the selection natively in Excel and copy and past the formula.

4

Hi I got hundreds of Excel file which cant easily copy and paste the formula, I am looking for some solution to do this.

5

Understood, but you’d only need to do what I suggest above on one of them, if they are all exactly the same format / layout.

Yet, again, this is an Excel issue not a PoSH one, though you are using PoSH to automate this. You still need to fully understand / work the Excel DOM to get what you are after.

So, this means:

  • knowing how each workbook is different and what if any built-in ranges are already there and using those range names.
  • Using the Excel DOM to dynamically create your own named ranges based in the number of rows / columns in the workbooks.
  • What about any nested/multi-sheet workbooks in the files set that you'd have to deal with.

    • Etc…