none
Exporting specific columns from XLS file to a new XLS or XLSX file using powershell RRS feed

  • Question

  • The scenario is that i have a file that our server is constantly dumping data to on a daily basis and out of all the data i only need 2 columns. I need to be a able to automate this because i need to generate a monthly report based on those two columns. That being said, i need to be able to extract two full columns from datafile1.xls to newdatafile.xlsx. If it would be easier if the original file is CSV i can easily save the data in CSV format instead of XLS. 

    Thanks in advance.

    Wednesday, June 25, 2014 1:14 PM

Answers

  • I see, im having a hard time executing the script, i keep getting the following error:

    '""Microsoft.Jet.OLEDB.4.0" has not been registered.";' 

    and when i try to run it like this i dont get any errors BUT nothing happens:

    C:\Windows\syswow64\rundll32.exe "C:\Program Files\Common Files\System\Ole DB\oledb32.dll",c:\temp\t.ps1

    This is the code, which to tell you the truth im not really that familiar with :\

    $strFileName = "C:\temp\original\styles.xls"
    $strSheetName = 'STYLES$'
    $strProvider = "Provider=Microsoft.Jet.OLEDB.4.0"
    $strDataSource = "Data Source = $strFileName"
    $strExtend = "Extended Properties=Excel 8.0"
    $strQuery = "Select  STYLE CODE ,  LAST UPDATE from [$strSheetName]"
    
    $objConn = New-Object System.Data.OleDb.OleDbConnection("$strProvider;$strDataSource;$strExtend")
    $sqlCommand = New-Object System.Data.OleDb.OleDbCommand($strQuery)
    $sqlCommand.Connection = $objConn
    $objConn.open()
    $DataReader = $sqlCommand.ExecuteReader()
    
    While($DataReader.read())
    {
     $ComputerName = $DataReader[0].Tostring() 
     "Querying $computerName ..."
     Get-WmiObject -Class Win32_Bios -computername $ComputerName
    }  
    $dataReader.close()
    $objConn.close()



    For both PowerShell or VBScript you are missing the libraries.  You need to download and install the ACE drivers.

    Search for ACE drivers.


    ¯\_(ツ)_/¯

    Wednesday, June 25, 2014 4:03 PM

All replies

  • Hi,

    If you're looking for pre-written scripts, you can check the repository:

    http://gallery.technet.microsoft.com/scriptcenter

    If not, you'll need to actually ask a question before we can help you.


    Don't retire TechNet! - (Don't give up yet - 12,950+ strong and growing)

    Wednesday, June 25, 2014 1:16 PM
  • $csv = "C:\temp\original\styles.csv"
    $xls = "C:\temp\new\NEWstyles.xls" 
    
    $xl = new-object -comobject excel.application
    $xl.visible = $true
    $Workbook = $xl.workbooks.open($CSV)
    $Worksheets = $Workbooks.worksheets
    
    $Workbook.SaveAs($XLS,1)
    $Workbook.Saved = $True
    
    $xl.Quit()

    That is all i can come up with now, how would i be able to extract two specific columns, say the two columns i need have headers "cost" and "address", i want to export those two columns including the headers to a new file called NEWstyles.xls


    Wednesday, June 25, 2014 1:26 PM
  • You have many choices.  You can delete the unneeded columns.  You can copy the Range to a new spreadsheet.  You can also just use ADO to query only those two columns into a file.

    Since this happens only once a month why is it that you need a script?

    I would just create an Excel report file that reads its data in from the external file and outputs the report.  That is something that Excel is very good at.


    ¯\_(ツ)_/¯

    Wednesday, June 25, 2014 2:12 PM
  • I could make a copy of the original file in another directory for archiving purposes and delete the unneeded columns from the work file, that is possible too. I just dont know how to go about looping for an entire column based on the header.

    I need a script because i want to automate this task, i want to just be able to set it and forget it because the report gets emailed to a certain department and i feel that it is more efficient to eliminate the user's responsibility to remember to extract the two columns in a new spreadsheet and email it to the department. With a script there is no room for human error (except for programmer error of course).

    Wednesday, June 25, 2014 2:23 PM
  • Use ADO and PowerShell to extract the two columns by name and email.

    A simple query will do this.

    select cost, address from [sheet1$]

    This will extract the columns.

    Here is a blog on how to do it: http://blogs.technet.com/b/heyscriptingguy/archive/2008/09/11/how-can-i-read-from-excel-without-using-excel.aspx


    ¯\_(ツ)_/¯

    Wednesday, June 25, 2014 2:30 PM
  • I see, im having a hard time executing the script, i keep getting the following error:

    '""Microsoft.Jet.OLEDB.4.0" has not been registered.";' 

    and when i try to run it like this i dont get any errors BUT nothing happens:

    C:\Windows\syswow64\rundll32.exe "C:\Program Files\Common Files\System\Ole DB\oledb32.dll",c:\temp\t.ps1

    This is the code, which to tell you the truth im not really that familiar with :\

    $strFileName = "C:\temp\original\styles.xls"
    $strSheetName = 'STYLES$'
    $strProvider = "Provider=Microsoft.Jet.OLEDB.4.0"
    $strDataSource = "Data Source = $strFileName"
    $strExtend = "Extended Properties=Excel 8.0"
    $strQuery = "Select  STYLE CODE ,  LAST UPDATE from [$strSheetName]"
    
    $objConn = New-Object System.Data.OleDb.OleDbConnection("$strProvider;$strDataSource;$strExtend")
    $sqlCommand = New-Object System.Data.OleDb.OleDbCommand($strQuery)
    $sqlCommand.Connection = $objConn
    $objConn.open()
    $DataReader = $sqlCommand.ExecuteReader()
    
    While($DataReader.read())
    {
     $ComputerName = $DataReader[0].Tostring() 
     "Querying $computerName ..."
     Get-WmiObject -Class Win32_Bios -computername $ComputerName
    }  
    $dataReader.close()
    $objConn.close()



    Wednesday, June 25, 2014 3:17 PM
  • Also im not sure if this would be any good, i found this script while reading a tutorial on ADO:

    On Error Resume Next
    
    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adCmdText = H0001
    
    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordSet = CreateObject("ADODB.Recordset")
    
    objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;"
        "Data Source=C:\temp\original\STYLES.xls;" 
            "Extended Properties=""Excel 8.0;HDR=Yes;"";" 
    
    objRecordset.Open "Select  STYLE CODE ,  LAST UPDATE FROM [Sheet1$]", _
        objConnection, adOpenStatic, adLockOptimistic, adCmdText
    
    Do Until objRecordset.EOF
        Wscript.Echo objRecordset.Fields.Item("Name"), _
            objRecordset.Fields.Item("Number")
        objRecordset.MoveNext
    Loop

    Only problem i have with it is that i get an error

    Missing statement body in do loop.
    At C:\temp\t.ps1:17 char:4
    + Do  <<<< Until objRecordset.EOF
        + CategoryInfo          : ParserError: (do:String) [], ParseException
        + FullyQualifiedErrorId : MissingLoopStatement
    My source: http://technet.microsoft.com/en-us/library/ee692882.aspx


    Wednesday, June 25, 2014 3:36 PM
  • If you do not know VBScript then use PowerShell.  Remove On Error line and fix initial errors.  You have failed the open.


    ¯\_(ツ)_/¯

    Wednesday, June 25, 2014 4:02 PM
  • I see, im having a hard time executing the script, i keep getting the following error:

    '""Microsoft.Jet.OLEDB.4.0" has not been registered.";' 

    and when i try to run it like this i dont get any errors BUT nothing happens:

    C:\Windows\syswow64\rundll32.exe "C:\Program Files\Common Files\System\Ole DB\oledb32.dll",c:\temp\t.ps1

    This is the code, which to tell you the truth im not really that familiar with :\

    $strFileName = "C:\temp\original\styles.xls"
    $strSheetName = 'STYLES$'
    $strProvider = "Provider=Microsoft.Jet.OLEDB.4.0"
    $strDataSource = "Data Source = $strFileName"
    $strExtend = "Extended Properties=Excel 8.0"
    $strQuery = "Select  STYLE CODE ,  LAST UPDATE from [$strSheetName]"
    
    $objConn = New-Object System.Data.OleDb.OleDbConnection("$strProvider;$strDataSource;$strExtend")
    $sqlCommand = New-Object System.Data.OleDb.OleDbCommand($strQuery)
    $sqlCommand.Connection = $objConn
    $objConn.open()
    $DataReader = $sqlCommand.ExecuteReader()
    
    While($DataReader.read())
    {
     $ComputerName = $DataReader[0].Tostring() 
     "Querying $computerName ..."
     Get-WmiObject -Class Win32_Bios -computername $ComputerName
    }  
    $dataReader.close()
    $objConn.close()



    For both PowerShell or VBScript you are missing the libraries.  You need to download and install the ACE drivers.

    Search for ACE drivers.


    ¯\_(ツ)_/¯

    Wednesday, June 25, 2014 4:03 PM
  • Thank you JRV for all your help.
    Thursday, June 26, 2014 1:33 PM
  • Were you able to find the drivers?


    ¯\_(ツ)_/¯

    Thursday, June 26, 2014 1:47 PM