Support Portal

for ProcessRobot and WinAutomation

Start a new topic

Filtering mass data

Hi Team,


I do have 50k raw data, and need do filtering data.

We have tried use :


1. Key and keyboards, and mixture with press window. the results didn't accurate.


2. Excel with IF function, it costs us around 2 hours to check the data.


3. Vb script doesn't work at our environment. And we don't want to keep Marco file for running the filtering data.


Is that any possibility or guide by using powershell? Powershell since more workable to our environment.


Michelle,

Use Exec SQL action with SQL Select against your large Excel.  It is very fast.

Each Worksheet Tab name is considered to be the From Table.

Only requirement is to put the column / header names in the 1st row of the Excel
You can do sql select, insert, updates.

You need to install the Microsoft ACE Driver for Exec SQL against Excel.
Search the forum for articles downloading and setting up the Driver


P.S.  For any future VBScriot need, there is a built-in Run VBScript action that holds the vbscript internally in an action, for execution

On my blog you have an example working with 100.000 lines (in my case, a list from a csv file).


Executing a sum operation on each line takes around 36 seconds. You can see some tools I have used, as Joseph recommends using some text/sql driver you can do your task instantly.


another method is to use some language to process/parse your task, I love to use some basic compiler like powerbasic or libertybasic, both really cheap.


https://scraper.es/abrir-archivos-csv/


translated:


https://translate.google.com/translate?hl=en&sl=auto&tl=en&u=https%3A%2F%2Fscraper.es%2Fabrir-archivos-csv%2F



1 person likes this
Michelle, Below is link to the forum article for using Sql against Excel using the buikt-in Exec Sql action of either Winautomation or ProcessRobot https://support.softomotive.com/support/solutions/articles/35000097667-execute-sql-queries-to-microsoft-excel
Login or Signup to post a comment