topic RE: Oledb if query in Product Forum

Oledb if query

VratHimbo — Thu, 25 Mar 2021 11:37:00 GMT

Hello guys,
just a quick explanation about Oledb..
I need to check if i've at least an empty value inside the row of an excel file included in a where clause.
FOR EXAMPLE:
Select * FROM Table1 WHERE invoice = [InvoiceID]
If exist at least one row from this query, i need to check if one of Column1, Column2 or Column3 is empty. Can i implement this check in the where clause? For ex. WHERE invoice = [InvoiceID] AND [Column1] != '' AND [Column2] != '' AND [Column3] != ''
I'll tried but it not works.. Any advice?
Thanks in advance

------------------------------
Vrat Himbo
------------------------------

RE: Oledb if query

PvD_SE — Thu, 25 Mar 2021 12:44:00 GMT

You write that it does not work. What does not work? Which stage action is at fault? What is the exception it throws?

Try this:
Assuming you have the connection string correct, done a connect and an open, including some wait stages, you might want to try and format the Excel Sheetname as [Table1$] instead of plain Table1.

SQL:
Your statement:
WHERE invoice = [InvoiceID] AND [Column1] != '' AND [Column2] != '' AND [Column3] != ''
...will return the row with the matching InvoiceID where comumns 1 and 2 and 3 all must have a value.
This does not seem to match what you describe.

You can put the check in the SQL but you can also only select on the InvoiceID and then have BP validate the collection that was returned.

The SQL might look like this:
WHERE invoice = [InvoiceID] AND ([Column1] = '' OR [Column2] = '' OR [Column3] = '')
...this would require at least one of the columns 1, 2 or 3 to be empty.

On the other hand is your description "one of Column1, Column2 or Column3 is empty" a bit fuzzy, in which case I'd like you to ask you to describe better what it is you want. 🙂

------------------------------
Happy coding!
Paul
Sweden
------------------------------

RE: Oledb if query

DaulatRam2 — Thu, 25 Mar 2021 12:56:00 GMT

Select * From [Table1$] Where [invoiceID]= "1234" AND ([Column1] <>'' OR [Column2] <>'' OR [Column3] <>'')
will filter for InvoiceID and at least one of Columns having value

Table1: is the name of the Sheet in excel file where you are checking for Invoice ID
invoiceID: is the name of the column in Table1 sheet within excel file.
1234: example Invoice ID you can use a data item stage as well instead of hardcoded value.
Column1,Column2,Column3 are names of the column

cheers

------------------------------
Daulat Ram
------------------------------

RE: Oledb if query

VratHimbo — Thu, 25 Mar 2021 15:33:00 GMT

Thanks, i need to check if i have empty values in 1 number column [column1] and in 2 string columns [column2] and [column3].
Which is the syntax in that case

------------------------------
Vrat Himbo
------------------------------

RE: Oledb if query

VratHimbo — Thu, 25 Mar 2021 15:40:00 GMT

Thanks Paul,
sorry if I have forgotten some things.
The problem with my query is that some columns are "Number", not only "String" type.
The second question is, in your opinion, is better to do with some queries or with a multi decision, where I go to check if the empty is empty or not? And in case throw a business exception?
Thx

------------------------------
Vrat Himbo
------------------------------

RE: Oledb if query

DaulatRam2 — Thu, 25 Mar 2021 15:44:00 GMT

Basically it will remain the same for empty columns

------------------------------
Daulat Ram
------------------------------

RE: Oledb if query

VratHimbo — Thu, 25 Mar 2021 16:08:00 GMT

I have this message when i try to perform OR [Column1] = ''

------------------------------
Vrat Himbo
------------------------------

RE: Oledb if query

DaulatRam2 — Thu, 25 Mar 2021 16:16:00 GMT

Try str([Column 1] )=''

------------------------------
Daulat Ram
------------------------------

RE: Oledb if query

VratHimbo — Thu, 25 Mar 2021 16:31:00 GMT

I have a syntax error 😞

------------------------------
Vrat Himbo
------------------------------

RE: Oledb if query

GopalBhaire — Fri, 26 Mar 2021 04:38:00 GMT

Hi Vrat,

Have you tried with IS NULL like [Column1] IS NULL

------------------------------
Gopal Bhaire
Analyst
Accenture
------------------------------

RE: Oledb if query

PvD_SE — Fri, 26 Mar 2021 07:18:00 GMT

Hi Vrat,

When extracting data from an Excel to a Collection, you generally have two options:
1. Use OleDB with SQL
2. Action 'Get Worksheet As Collection' on the 'MS Excel VBO'

With the OleDB option you can at the same time select what data you want to have. With the 'Get Worksheet As...' option you get all data and have to filter your collection later to remove the data you do not want to have.

I only use the OleDB option for Excels that contain a large number of rows and a lot of selection criteria. The reason for this is that filtering large collections with the 'Filter Count Collection' (the action I use) does not work so smooth as you might think. It does not handle large numbers or rows well and ends up in OutOfMemory exceptions. As a result of that, I divide my collection in lesser collection s and filter each small collection instead of the big one. This works nice but is not fast.

After a recent battle with OleDB, I concluded that even OleDB does not handle large number of rows well and crashes. A general BP problem perhaps? So even when working with OleDB I split up the original Excel in smaller Excels and run OleDB on each smaller Excel to get the results I need. Selecting rows with OleDB is always fast.

What is a large Excel and what are many rows?
Depends from case to case. In my case, 15k rows for a 10-column Excel was the upper-limit to avoid errors in OleDB. SO I split my 60k+ row Excel in 15k Excel files that I feed in a loop to OleDB. That worked fine and fast.

Remark:
When posting your question as specific as you did, with a description and the SQL you use, make sure to describe more exactly what you want, as any answer will only be as good as the question. 🙂

------------------------------
Happy coding!
Paul
Sweden
------------------------------