How to manipulate data using OLE DB

Help Activities
, ,
1

Hi,

I have a set of excels which I want to manipulate to get the output file. The file has over 1 lakh rows and it takes more than 15-20 mins to open and hence the complete process takes around 4.5 hrs. I wanted to use Ole DB to excel as a database. How can I do the following operations :

  1. Copy data from multiple files and paste in one.
  2. Use vlookup from another sheet to current sheet containing over 1 lakh rows.
  3. Copy data on which vlookup is applied and paste it to a template.
  4. Refresh the pivot table (in-built in template).

Kindly let me know if copy-pasting, vlookup, refreshing sheet is possible using OleDB and how can we do this.

2

@loginerror @HareeshMR Any suggestions?

3

Hi @Pankit ,

Would you be so kind as to tell us how much time just reading the excel files will take?

Usually for large files, it would be best to use the Excel Application Scope since it is tailor made for Excel Operations, which means it will take less time for an EAS(Switch off Visibility) to read a dataset than it would take for a Workbook Read Range Activity to.

If it doesn’t take more than 2-3 minutes, then we may develop the solution along a different trajectory.

Kind Regards,
Ashwin A.K

4

Opening the file takes more than 10-15 mins… I am just trying to use Ole Db actually. My question is specific to Ole Db use… Just to figure out if the ops can be done using Oledb

5

Hi @Pankit

I believe there were some topics on the Forum on how to connect to an Excel file with the Database activities, so that should be doable at least.