Excel

Hello!

You can use the Query from Excel AC with the below configuration. I've tested this on my own sheet and it works but if you'll be kind enough to attach your sample data I can test on that for you and send some example results.

This is the data I used in Sheet1

And the results from my sample data

I tried Excel AS, but did not find how to use it, in Advanced Commands there is no description of the Select commands and WHERE how to use them, is there a guide for Select and Where??

Unfortunately, I cannot grant the file I have no such permissions ?

I already thought to do this function through macros or through a database?

the function "query from excel" would help me with my tasks if there was a small reference on Select and WHERE queries?

You can find the advanced commands reference guide on KryoNet here:

https://kryonsystems.force.com/KryoNet/s/article/Kryon-RPA-Version-19-5

In the AC the SELECT and WHERE clauses are standard SQL except that you must follow the formatting instructions shown in the AC itself i.e. for fields you must enclose in square brackets [] and parameters (if set in the parameters section) must be preceded with @

But, the solution does work as far as i can see, and it may be a bit easier than using a macro or DB!

If the parameter is a string you must (just like regular SQL) enclose in quotes "..." and for numeric parameters quotes are not required.

Darren, you are the best!, Your advice always helps me!)

database and macros is a workaround ,I will try, I have many such requests

Can I use the column letter instead of the column name? Since this is a robot, it will scan a large number of files, and the row names will be different, but the letter of the column will not change, I am trying to make a dynamic robot instead of a static one

Yes, you have to deselect the "table contains column headers" option and use [F1] for column 1, [F2] for column 2 and so on...

[Document Type] IN ("RX","DR") AND [Document Number] IS NULL AND [Amount in local currency] MIN

Gives an error message

[A1] IN ("RX","DR") AND [G1] IS NULL AND [J1] MIN

tried many options, I am a student - I can be wrong.?

Without MIN outputs values

Hi, what is your objective with this code, to return the smallest value in the column, similar to this on w3schools?

https://www.w3schools.com/sql/sql_min_max.asp

yes, return the smallest value.

I realized my mistake, the max and min function is available only for SELECT, the question remains how to display more fields in SELECT ....

When specifying the parameter [F]>

Hi, here's an example, using the sample data I provided earlier this will return the value "2". I don't believe you can have more than one column returned when used with MAX or MIN, because SQL doesn't allow that, though I am happy to be corrected by a more experienced SQL expert here in the forums. The reason you get a error when using [Fx] with MIN or MAX is because the checkbox "table contains column headers" is unchecked. This means that you will be feeding the headers (which includes A-Z) into a function which only accepts numbers. If you wish to use MIN/MAX whilst also using [Fn] you must start the data from row 2 under the "a specific range" option in the AC.

Thank you for helping, good support! If it weren't for your help, the project would not have been able to be implemented

You're welcome 🙂 If you need help again don't hesitate to post!

How to use Define the parameters for your SQL query in Query from excel ?

There are 3 variables created using Query from excel, if one of them is empty, then the addition (addition and subtraction)of these variables does not occur(mathemathics)

how can you make variable numeric after Queru from excel function

Hi,

Do you mean something like this? You need to first specify the parameter at the top and select the most appropriate type for it, then reference it in the WHERE clause using the @ notation. Note for text types such as Product in the example, you don't need to use enclosing quotes. Nor do you have to use every parameter defined.

For your second part, where you have the results after the Query from Excel, numbers (whether integer or float) are already numeric. Clearly, if one of them is empty (null) then you need to redefine that value as 0, perhaps using an IF statement, since you cannot do calculations on null or empty values.

it's very interesting but I didn't understand anything))

Variables were created with a value of 0, not empty

Please can you provide me with a screenshot of the Mathematics AC which you say is not working, and what the values of each variable inside it is? I'd like to do a test on my side.

Additionally, when you say "There are 3 variables created using Query from excel" what do you mean by this? SQL queries return rows of data, where the columns and rows are delimited as you specify. All of these rows - whether just one or several - are stored in the single variable you specify inside the Query from Excel AC.