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

Welcome Forums General PowerShell Q&A How to import specific column from all the excel files and export it as a table

This topic contains 4 replies, has 3 voices, and was last updated by

 
Participant
6 months, 2 weeks ago.

  • Author
    Posts
  • #98223

    Participant

    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")

  • #98224

    Participant

    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.

  • #98235

    Participant

    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.

  • #98236

    Participant

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

    • #98272

      Participant

      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...

The topic ‘How to import specific column from all the excel files and export it as a table’ is closed to new replies.