IT Process Automation

  • 1.  How to read data from .xls and .xlsx file using PAM.

    Posted Nov 19, 2015 07:48 AM

    Hi Team,

     

    I am trying to read data from .xls and .xlsx file from pam but the data is coming as encrypted. Although I am successfully read the data from .csv,.txt .If anyone have any idea please guide me. Any help will be appreciated.

     

    Thanks & Regards,

    Padmini



  • 2.  Re: How to read data from .xls and .xlsx file using PAM.
    Best Answer

    Posted Nov 20, 2015 09:58 AM

    Hi Padmini,

     

    Both .XLS and .XLSX are not plain text files, they are Microsoft Excel encoded (shouldn't be encrypted though, unless someone applied a password to do so).

     

    I have been able to access data from within Excel spreadsheets in CAPA by using a PowerShell script along with the Microsoft Access Runtime Engine.

     

    The MS Access Runtime Engine is available directly from Microsoft, I have it installed on the Agent server where I execute the script operator (I also happen to have it installed on both my CAPA Nodes purely for my own convenience).

     

    Download Microsoft Access 2013 Runtime from Official Microsoft Download Center

     

    Here is an example of the PowerShell script that I use to read in the data from the spreadsheet, personally I find it easier to read the Excel Spreadsheet, and store it in a temp SQL DB table so I can query against it.  But it all depends on what you want to do with the data that you pull out of Excel.

     

    There is a lot of existing documentation out there for how to run queries against Excel using the engine, so just google it if you need more reference guidelines.

     

    Here are a few places to get you started:

    Hey, Scripting Guy! How Can I Read from Excel Without Using Excel? - Hey, Scripting Guy! Blog - Site Home - TechNet Blog…

    Use ACE Drivers and PowerShell to Talk to Access and Excel - Hey, Scripting Guy! Blog - Site Home - TechNet Blogs

    Using Microsoft ACE for direct Excel .xlsx WFA Data-Sources - NetApp Community

     

    Hope this helps!

    Ian

     

     

    $strFileName = "**PATH TO THE EXCEL FILE**"

    $strProvider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$strFileName`";Extended Properties=`"Excel 12.0 Xml;HDR=YES`";"

    $conn = new-object System.Data.OleDb.OleDbConnection($strProvider)

    $conn.open()

     

    $ds = New-Object system.Data.DataSet

    $cmd = new-object System.Data.OleDb.OleDbCommand

    $cmd.Connection = $conn

    $da = new-object System.Data.OleDb.OleDbDataAdapter

     

    $Table = "**NAME_OF_THE_TAB**$"

    $qry = "select * from [{0}]" -f $Table;

    $cmd.CommandText = $qry;

    $da.SelectCommand = $cmd;

    $dt = new-object System.Data.dataTable("$($Table)");

    $null = $da.fill($dt);

     

    $dt