search for a value in an Excel or SQL file

How can I search for a value in an Excel or SQL file? I want to search for a value in a very large file and repeat this operation several times case by case

Hi @alex1368 -

This is kind of a vague question. Can you be more specific about your input and desired output?

6 Likes

I am sorry for my late reply
I have an excel or sql file that I want to search for a numerical value (such as a phone number) or a name or a sentence.
For example, I have a phone book that I want to search in

It would be better if you provide example of your input data. Even screen short. Your Excel may have separate columns with phone numbers or it may be coma separated list so on.
As for SQL file, what database do you have in mind? KNIME has limited number of SQL sources to be read. Is it MS-Access DB file? What the data structure in it?

3 Likes

thanks for your answer
The file I want is the attached file
This is my Excel file which is loaded in the SQL database with this model and format.
I want to search for a number or name or… through knime nodes from inside these files.
New Microsoft Excel Worksheet (2).xlsx (8.8 KB)

My question is very simple
I have a file like a phone book that I want to search for a number or a name
Sample file
New Microsoft Excel Worksheet (2).xlsx (8.8 KB)

@alex1368 one option would be to use the String Manipulation (Multi Column) node.

kn_forum_46146_search_in_all_columns.knwf (19.7 KB)

2 Likes

Thankful
Can you explain how to use this node?
I mean how to search a number from inside the phone book
How to work with this node?

@alex1368 you might want to read the description of the node and check the example. You can apply a function to multiple columns at a time. In this case the position of a possible part of the column (a phone number) is being searched and the position (in the sample always the first position =0) is brought back If the item is not found a -1 is being provided. So you can see in which columns at wich row at which position your phone number is being found.

1 Like

Hi @mlauber71 @ScottF ,
sorry, I want to ask something about String Manipulation (Multi Column) node.
Can the String Manipulation (Multi Column) node perform manipulation on two columns at the same time?
in the picture below, I have two columns that are a must to manipulate. “1” to be Male, and “A” to be “40”
image
but I don’t know the syntax I have to enter to solve the problem

To manipulate 2 columns you need Column Expression node. It may need to be additionally installed.

2 Likes

If you don’t want to use 2 multi column nodes then you could also first combine the columns do your replacement and split them back I guess
br

1 Like

thanks for your help @izaychik63 & @Daniel_Weikert , my problem is solved.

best regards,
Veni

1 Like