Data input filter to speed up workflow

Question

Hi all,

I have a workflow which brings in 1 .csv file with 6m rows of data and about 150 columns.  It is split into two years 2020 and 2021.  My workflow only needs the 2021 data.  Am i able to split the data using an SQL statement such as (SELECT * FROM 'file' WHERE YEAR = 2021) to only bring part of the input data in?

This would have the data coming in and speed up the workflow, also, if i could modify the SELECT, i could then just bring in the columns that i need.

Any ideas?

Many thanks.

David.

Qiu · Answer

@DGK1981

From my little experience, there is not much we can do when facing Excel.

So maybe you could do something in Excel, like sort by year, and define a range only for the data you need.

DGK1981 · Answer

Hi Qiu,

Thanks for the tips.

I always cache the workflow, but it still takes nearly 1 hour in the first instance.  I do need all the records as the base data is not in year order.

Is there no other way to filter data before the workflow?

David.

Qiu · Answer

@DGK1981

I dont know how can we apply SQL query while importing Excels.

As an alternative,

* Limit the record limit when you develop your workflow
* After Input tool, put a Select tool then select Cache and Run.
* If you could define a Range for the data you want in Excel, you can then choose to only import that range