How to check for duplicate values in Excel?
Hi Team,
I have a requirement viz. The robot has to filter the data according to some criteria and after filtering it has to check for duplicates. The excel contains a minimum of 100 rows after filtering.
Questions:
How to filter the columns in automation?
How to check for duplicates when there is no pre specified rules or conditions?
***Updated by moderator: Lochan to update Categories***
**Moderation Team has archived post**
This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.
Accepted Solution
Accenture
IN
Hi Malini,
The connection string which i provided is for xls file format. U need to change Extended Properties=Excel 8.0 xml .
Try by changing this.
xls file : Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\sivaraj.a.muthusamy\Documents\Book1.xls;Mode=ReadWrite;Extended Properties=Excel 8.0;Persist Security Info=False
xlsx file : Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\sivaraj.a.muthusamy\Documents\Book1.xlsx;Mode=ReadWrite;Extended Properties=Excel 8.0 xml;Persist Security Info=False
If not working try google search for connection string in Excel using oledb. Because connection string will differ PC to PC depends on installation.
Accenture
IN
Hi Malini,
The best & Fastest way to interact with excel in openspan is OledbQuery component if the Excel has reasonable Table structure.
In oledb query you can write sql queries post creating connection to the excel.
Depends on Excel format(.xls,.xlsx) Connection string will be differed.
In Commandtext part write your sql query with Distinct Keyword which removes your duplicates.
GetTable() Method of Oledbquery component return DataTable. Hence your result can be loop through DataTable.
You need to call Execute() method before GetTable().
Larsen & Toubro Ltd
IN
Hi Sivaraj,
Thank you for your reply. I tried in the way that you mentioned. But I am getting an error while I tested the query using the Test Query link. I have attached the screenshot. Am I making a mistake somewhere? Please guide me.
Accepted Solution
Accenture
IN
Hi Malini,
The connection string which i provided is for xls file format. U need to change Extended Properties=Excel 8.0 xml .
Try by changing this.
xls file : Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\sivaraj.a.muthusamy\Documents\Book1.xls;Mode=ReadWrite;Extended Properties=Excel 8.0;Persist Security Info=False
xlsx file : Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\sivaraj.a.muthusamy\Documents\Book1.xlsx;Mode=ReadWrite;Extended Properties=Excel 8.0 xml;Persist Security Info=False
If not working try google search for connection string in Excel using oledb. Because connection string will differ PC to PC depends on installation.
Larsen & Toubro Ltd
IN
HI Sivaraj,
Thank you for your help. I successfully executed the Query using the below connection string.
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\1570053\Documents\Weekly Update.xlsx;Mode=ReadWrite|Share Deny None;Extended Properties=Excel 12.0;Persist Security Info=False
I got an error saying "External table is not in expected format" error before I used this connection string. I resolved it with the help of the below URL. I am linking the URL for easy reference to all:
https://stackoverflow.com/questions/1139390/excel-external-table-is-not-in-the-expected-format
Accenture
IN
Royal Bank of scotland
IN
Hi Malini / Sivaraj,
if possible Could you please post a screenshot of your or similar simple automation and how does Robot recognize the column name for select query from Excel