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