none
Powershell? Copy multiple .csv files into one workbook

    السؤال

  • I have a powerhsell script (thanks to the scripting guys forum!) that exports mailbox data from multiple servers.  It creates one .CSV file for each server.  I need to copy each of the CSV files into individual tabs in a workbook.

    19/جمادى الأولى/1431 10:38 م

الإجابات

  • Check this out: http://gallery.technet.microsoft.com/ScriptCenter/en-us/dc312289-f566-42f5-9116-34bc277a9230. An altered version of that script is possibly what you are looking for.   

     

    Craig

    • تم الاقتراح كإجابة بواسطة CraigLieb 08/رمضان/1431 06:09 م
    • تم وضع علامة كإجابة بواسطة Boe ProxMVP, Moderator 21/رمضان/1431 11:51 م
    08/رمضان/1431 06:09 م
  • Try this on for size. You need to change the location of your CSV files. I am assuming they are all in one folder.

    # Script name: Copy-CsvToExcel.ps1
    # Created on: 3/21/2011
    # Author: OldDog1
    # Purpose: How can I use Windows Powershell to
    # copy Selected CSV Files to one Excel WorkBook?
    #-----------------------------------------------------
    function Release-Ref ($ref) {
    ([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
    [System.__ComObject]$ref) -gt 0)
    [System.GC]::Collect()
    [System.GC]::WaitForPendingFinalizers() 
    }
    #-----------------------------------------------------
    $xlPasteValues = -4163          # Values only, not formulas
    $xlCellTypeLastCell = 11        # to find last used cell

    $xl = new-object -comobject excel.application
    $xl.Visible = $True
    $xl.DisplayAlerts = $False
    $wb = $xl.Workbooks.Add()
    $i = 1
    $collection = Get-ChildItem G:\Scripts\* -include *.csv  # Change the location of your CSV files here.

    $length = 4

    foreach ($item in $collection) {
     $wb1 = $xl.Workbooks.Open("$item")
     $array = $item.ToString()
     $delim = "\"
     $SheetName = $array.split($delim)
     $s = $SheetName[2]
     $sn = $s.split(".")
     $nsn = $sn[0]
     $ws1 = $wb1.worksheets | where {$_.name -eq $nsn}
    Write-Host $item $nsn
    $used = $ws1.usedRange
    $used.Select()
    $used.copy()
    $wb.Activate()
    $ws = $wb.Sheets.Add()
    $ws2 = $wb.worksheets | where {$_.name -eq "sheet$i"}
    [void]$ws2.Range("A1").PasteSpecial(-4163)
    $ws2.name = $nsn
    $i++ 
    $wb1.Close()
      
    }

    # $xl.quit()

    # $a = Release-Ref($ws)
    # $a = Release-Ref($wb)
    # $a = Release-Ref($xl)

    • تم الاقتراح كإجابة بواسطة OldDog1 17/ربيع الثاني/1432 01:14 ص
    • تم وضع علامة كإجابة بواسطة CSafreed 23/ربيع الثاني/1432 06:28 م
    16/ربيع الثاني/1432 07:51 م

جميع الردود

  • Check this out: http://gallery.technet.microsoft.com/ScriptCenter/en-us/dc312289-f566-42f5-9116-34bc277a9230. An altered version of that script is possibly what you are looking for.   

     

    Craig

    • تم الاقتراح كإجابة بواسطة CraigLieb 08/رمضان/1431 06:09 م
    • تم وضع علامة كإجابة بواسطة Boe ProxMVP, Moderator 21/رمضان/1431 11:51 م
    08/رمضان/1431 06:09 م
  • Is this still an open item for you?  Have you had a chance to review the link that Craig posted here?  If we do not here back from you in 5 days, we will assume that this has been resolved and will mark it as such.  If it is still open, please let us know and we will do what we can to help you out. Thanks.
    17/رمضان/1431 02:23 ص
  • Yes.  I hadn't gotten any response in a long time so I haven't been monitoring the post as closely. 

    @Craig.  I checked out the link but don't see how to get csv's (or any simple text based files) to populate each tab.  That is, if I have file1.csv, file2.csv, file3.csv, how can I apply what's listed in the link to make a sheet (Excel tab) per csv?

    Thanks, Colin

    12/شوال/1431 06:35 ص
  • Check out Boe's script:

    http://gallery.technet.microsoft.com/ScriptCenter/en-us/7c56c444-2476-4625-b1d9-821f30280e44

    Karl


    http://unlockpowershell.wordpress.com
    -join("6B61726C6D69747363686B65406D742E6E6574"-split"(?<=\G.{2})",19|%{[char][int]"0x$_"})
    12/شوال/1431 02:36 م
  • Karl - thank you for the TechNet article. It appears to be very comprehensive. I must apologize but I'm not a programmer so much of what is contained in the script is above my head.

    Can you explain this line: [regex]$regex = "^\w\:\\"
    I did a little research and see that it's a regular expression but I don't understand what's in the quotes.

    I have several maniplutations that I want done with this export. The script creates a csv for each of our Exchange servers with the current date in the file name. Here's what I really looking for:

    My export creates the following filenames/paths:

    c:\temp\Exchange2003_server1.csv, c:\temp\Exchange2003_server2_m-dd-yyyy.csv, c:\temp\Exchange2003_server3_m-dd-yyyy.csv, c:\temp\Exchange2003_server4_m-dd-yyyy.csv, c:\temp\Exchange2007_server1_m-dd-yyyy.csv, c:\temp\Exchange2007_server2_m-dd-yyyy.csv, c:\temp\Exchange2007_server3_m-dd-yyyy.csv, c:\temp\Exchange2007_server4_m-dd-yyyy.csv

    remove the first row of data from each CSV

    remove columns two and three from half of the CSVs (c:\temp\Exchange2003_server1.csv, c:\temp\Exchange2003_server2_m-dd-yyyy.csv, c:\temp\Exchange2003_server3_m-dd-yyyy.csv, c:\temp\Exchange2003_server4_m-dd-yyyy.csv)

    'import' each CSV file into its own tab into an existing XLSX workbook (named c:\reports\ExchangeStatus-m-dd-yyyy.XLSX) while renaming the tab name (e.g. Exchange2003_server1m-dd-yyyy.csv = tab called Ex2003_1)

    format columns B, C of each sheet in the XLSX doc as number format with coma seperater for thousands and zero decimal places

    16/ربيع الثاني/1432 05:28 م
  • Try this on for size. You need to change the location of your CSV files. I am assuming they are all in one folder.

    # Script name: Copy-CsvToExcel.ps1
    # Created on: 3/21/2011
    # Author: OldDog1
    # Purpose: How can I use Windows Powershell to
    # copy Selected CSV Files to one Excel WorkBook?
    #-----------------------------------------------------
    function Release-Ref ($ref) {
    ([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
    [System.__ComObject]$ref) -gt 0)
    [System.GC]::Collect()
    [System.GC]::WaitForPendingFinalizers() 
    }
    #-----------------------------------------------------
    $xlPasteValues = -4163          # Values only, not formulas
    $xlCellTypeLastCell = 11        # to find last used cell

    $xl = new-object -comobject excel.application
    $xl.Visible = $True
    $xl.DisplayAlerts = $False
    $wb = $xl.Workbooks.Add()
    $i = 1
    $collection = Get-ChildItem G:\Scripts\* -include *.csv  # Change the location of your CSV files here.

    $length = 4

    foreach ($item in $collection) {
     $wb1 = $xl.Workbooks.Open("$item")
     $array = $item.ToString()
     $delim = "\"
     $SheetName = $array.split($delim)
     $s = $SheetName[2]
     $sn = $s.split(".")
     $nsn = $sn[0]
     $ws1 = $wb1.worksheets | where {$_.name -eq $nsn}
    Write-Host $item $nsn
    $used = $ws1.usedRange
    $used.Select()
    $used.copy()
    $wb.Activate()
    $ws = $wb.Sheets.Add()
    $ws2 = $wb.worksheets | where {$_.name -eq "sheet$i"}
    [void]$ws2.Range("A1").PasteSpecial(-4163)
    $ws2.name = $nsn
    $i++ 
    $wb1.Close()
      
    }

    # $xl.quit()

    # $a = Release-Ref($ws)
    # $a = Release-Ref($wb)
    # $a = Release-Ref($xl)

    • تم الاقتراح كإجابة بواسطة OldDog1 17/ربيع الثاني/1432 01:14 ص
    • تم وضع علامة كإجابة بواسطة CSafreed 23/ربيع الثاني/1432 06:28 م
    16/ربيع الثاني/1432 07:51 م
  • I tried the script after changing the location of G:\scripts to the location where my files are.  It created a new workbook with total of 11 tabs.  The folder has 8 CSVs.  Sheet was was renamed to "MyTest".  Which is the in the folder structure where the files live (c:\temp\MyTest).  The script errored numerous times with "Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic." and one cannot call null valued exception on line 38: 11.  Here's that line:

    $used

     

    .copy()

    Here's the exact errors. NOTE: the cannot rename repeats several times but I just copied it once. 

    C:\Temp\MyTest\Exch2003Report-3-21-2011\ex1-3-21-2011.csv MyTest

    You cannot call a method on a null-valued expression.

    At C:\Users\82479\AppData\Local\Temp\33e04627-c45c-4cc8-a270-c4a104bcf6f4.ps1:37 char:13

    + $used.Select <<<< ()

        + CategoryInfo          : InvalidOperation: (Select:String) [], RuntimeException

        + FullyQualifiedErrorId : InvokeMethodOnNull

     

    You cannot call a method on a null-valued expression.

    At C:\Users\82479\AppData\Local\Temp\33e04627-c45c-4cc8-a270-c4a104bcf6f4.ps1:38 char:11

    + $used.copy <<<< ()

        + CategoryInfo          : InvalidOperation: (copy:String) [], RuntimeException

        + FullyQualifiedErrorId : InvokeMethodOnNull

     

    Exception calling "PasteSpecial" with "1" argument(s): "PasteSpecial method of Range class failed"

    At C:\Users\82479\AppData\Local\Temp\33e04627-c45c-4cc8-a270-c4a104bcf6f4.ps1:42 char:36

    + [void]$ws2.Range("A1").PasteSpecial <<<< (-4163)

        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException

        + FullyQualifiedErrorId : ComMethodTargetInvocation

     

    C:\Temp\MyTest\Exch2003Report-3-21-2011\ex2-3-16-2011.csv MyTest

    You cannot call a method on a null-valued expression.

    At C:\Users\82479\AppData\Local\Temp\33e04627-c45c-4cc8-a270-c4a104bcf6f4.ps1:37 char:13

    + $used.Select <<<< ()

        + CategoryInfo          : InvalidOperation: (Select:String) [], RuntimeException

        + FullyQualifiedErrorId : InvokeMethodOnNull

     

    You cannot call a method on a null-valued expression.

    At C:\Users\82479\AppData\Local\Temp\33e04627-c45c-4cc8-a270-c4a104bcf6f4.ps1:38 char:11

    + $used.copy <<<< ()

        + CategoryInfo          : InvalidOperation: (copy:String) [], RuntimeException

        + FullyQualifiedErrorId : InvokeMethodOnNull

     

    Exception calling "PasteSpecial" with "1" argument(s): "PasteSpecial method of Range class failed"

    At C:\Users\82479\AppData\Local\Temp\33e04627-c45c-4cc8-a270-c4a104bcf6f4.ps1:42 char:36

    + [void]$ws2.Range("A1").PasteSpecial <<<< (-4163)

        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException

        + FullyQualifiedErrorId : ComMethodTargetInvocation

     

    Exception setting "Name": "Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic.

    "

    At C:\Users\82479\AppData\Local\Temp\33e04627-c45c-4cc8-a270-c4a104bcf6f4.ps1:43 char:6

    + $ws2. <<<< name = $nsn

        + CategoryInfo          : InvalidOperation: (:) [], RuntimeException

        + FullyQualifiedErrorId : PropertyAssignmentException

     

     

     

     

     

    18/ربيع الثاني/1432 03:45 م
  • OK, looks like we need to do more tweeking.

    The script thinks the file name is in the 3rd possition. Looks like it is in the 4th.

    $array = $item.ToString()
     $delim = "\"
     $SheetName = $array.split($delim)
     $s = $SheetName[2]  # <---- Change this to [3]

    Excel always opens three worksheet, so that accounts for the 3 extra sheets, you will just have to delete them.

    18/ربيع الثاني/1432 04:25 م
  • Thank you! That worked to get them into one Excel Workbook. A have a few more things I want to automate / tweek.

    · Partially automate the folder path and filename based on the current system date in format m-dd-yyyy (e.g. c:\temp\ Exch2003Report-m-dd-yyyy\Exchange2003_server1_3-24-2011.csv

    · Remove the first row of data from all eight CSVs

    · Remove columns two and three from half of the CSVs (c:\temp\Exch2003Report-m-dd-yyyy\Exchange2003_server1_m-dd-yyyy.csv, c:\temp\Exchange2003_server2_m-dd-yyyy.csv, c:\temp\Exchange2003_server3_m-dd-yyyy.csv, c:\temp\Exchange2003_server4_m-dd-yyyy.csv)

    · Format columns B, C of each sheet in the XLSX doc as number format with coma separator for thousands and zero decimal places

     

    Can you explain where in the script it’s pulling information from the CSV into its own tab? I have a similar task I’d like to do that will information from a CSV into an existing tab of an existing Excel doc. Should that be a new question?

    19/ربيع الثاني/1432 05:50 م
  • As I am not at all sure about all of what you are asking, I will give you the parts and pieces.

    1. Date format:    $Date = (Get-Date -format "MM-dd-yyyy")

    2. Remove the first row of data from all eight CSVs.:

     to delete a row where $y is the row number:

    Function DelRow($y) { 
      $Range = $ws1.Cells.Item($y, 1).EntireRow
         [void]$Range.Select()
         [void]$Range.Delete()
    }

    3.  Format columns:

    $ws1.Range("B:C").NumberFormat = "#,##0"

    "Can you explain where in the script it’s pulling information from the CSV into its own tab? I have a similar task I’d like to do that will information from a CSV into an existing tab of an existing Excel doc. Should that be a new question?"

    When Excel opens a CSV it opens it in a NEW Workbook, so what I do is open both the CSV file and the Excel workbook, copy the information from the CSV and then paste it into a new worksheet in the Excel Workbook.

    $used = $ws1.usedRange # $ws1 is the worksheet that the CSV file opens.
    $used.Select() # select everything on that sheet
    $used.copy() # copy it
    $wb.Activate() # Switch to the Excel sheet
    $ws = $wb.Sheets.Add() # add a new worksheet
    $ws2 = $wb.worksheets | where {$_.name -eq "sheet$i"} # select the new sheet and ID it as $ws2
    [void]$ws2.Range("A1").PasteSpecial(-4163)  # Paste the info from the CSV into the new sheet.

    So, you could open your existing workbook, select the corect tab and Cell and then paste the info from the CSV.

    19/ربيع الثاني/1432 06:44 م
  • Thanks for being patient. You're responses are very helpful. And quick!  Please set me straight if my forum etiquette is improper.  Here’s what I have left:

     

     

    1. Date format.  Perfect.  I was trying to using a date variable to tell the script the path the file I want to you. The $Date resolved that.

    I did this:

    $Date = (Get-Date -format "M-dd-yyyy")

    ...

    $collection = Get-ChildItem C:\Temp\MyTest\Exch2003Report-$Date\* -include *.csv

    2. Remove the first row of data from all eight CSVs.  I need more help here. I need to remove the first row of each CSV.  I’m not sure where to use this block or if /when to declare/specify the value for $y.  It ends up being row 1 in the new excel sheets

    Function DelRow($y) {
    $Range = $ws1.Cells.Item($y, 1).EntireRow
    [void]$Range.Select()
    [void]$Range.Delete()
    }

    I want to remove the first line of each CSV that is ultimately being imported into Excel.

    3. Remove columns B and C from 4 of the 8 CSVs.  Here are the paths:

    c:\temp\ Exch2003Report-$Date\Exchange2003_server1_$Date.csv

    c:\temp\ Exch2003Report-$Date\Exchange2003_server2_$Date.csv

    c:\temp\ Exch2003Report-$Date\Exchange2003_server3_$Date.csv

    c:\temp\ Exch2003Report-$Date\Exchange2003_server4_$Date.csv

     


    4. Format columns.  This needs to be done after removing columns B and C (above).  Where would this go this script.

    $ws1.Range("B:C").NumberFormat = "#,##0"

     

    19/ربيع الثاني/1432 10:34 م
  • #2

    " I’m not sure where to use this block or if /when to declare/specify the value for $y.  It ends up being row 1 in the new excel sheets

    Function DelRow($y) {
    $Range = $ws1.Cells.Item($y, 1).EntireRow
    [void]$Range.Select()
    [void]$Range.Delete()
    }

    I want to remove the first line of each CSV that is ultimately being imported into Excel."

    Functions go at the top of the script, so make  this block the first thing in your script.

    In this case $y = 1 because you want the first row. You can declare/specify the value for $y just before you use it.

    I would put it right after you paste the info into the new sheet:

    [void]$ws2.Range("A1").PasteSpecial(-4163)

    $y = 1

    DelRow($y)

    And before you close the CSV.

    #3

    Removing a column is easy enough:

     $Rng = $ws2.Range("B:C")

     [void]$Rng.Select()
    [void]$Rng.Delete()

    But chosing specific worksheets is a little harder.

    If it really is server 1,2,3,4
    You could try:
     
    for ($i=1; $i -lt 4; $i++) {
    $ws = $wb.worksheets | where {$_.name -eq "Exchange2003_server$i_$Date"}
    $ws.select()
    $Rng = $ws.Range("B:C")

     [void]$Rng.Select()
    [void]$Rng.Delete()

    $ws.Range("B:C").NumberFormat = "#,##0"

    }

    • تم الاقتراح كإجابة بواسطة OldDog1 20/ربيع الثاني/1432 11:53 م
    20/ربيع الثاني/1432 03:47 م