Answered

searching in csv file

(0) Share

Report

Posted on by GregPL

Hi, I have searched everywhere but have not found a suitable answer. The AI didn't help either, so I'm going to ask you guys.

My problem is that I want to search for the phrase "SP" in a rather large table. After searching for it in a column, I want the value of the row to be imported into a variable that uses information about the location of the cell to give me its value.

Screenshot 2024-03-01 100029.png

Everything works perfectly, but only when I manually enter the value I am looking for (34) -> then I get the value I am looking for (price0). When I try to automate the whole thing, I get an error. I was trying with subtext and also nothing 😞 Is there a way around this?

Screenshot 2024-03-01 101848.png

Thanks in advance for your advice!
Have a nice day

Categories:

Power Automate Desktop

All responses (4)

Answers (1)

Sort by

CU16071609-1 6,255 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@GregPL

What does the CSV file look like? Could you please share a screenshot, if possible, after concealing any sensitive information?
GregPL 6 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Deenuji,
it will not be easy to remove sensitive data, because the whole document is sensitive... 😉
I did my best!

I know which column the data (thats why i have there Column6) is in, but the position in the row changes. Once the value can be searched higher and once lower.... Therefore, it is necessary to search for a phrase (to determine the row of data) and implement it in final variable -> Price0.

Thx for your help!
Verified answer

CU16071609-1 6,255 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@GregPL ,
The error is being thrown because a string value is being passed as an index. I've made appropriate conversions in the code below. Please review it, and feel free to ask if you have any questions.

Code:
File.ReadFromCSVFile.ReadCSVWithCustomSeparator CSVFile: $'''C:\\Users\\Downloads\\_20240.csv''' Encoding: File.CSVEncoding.UTF8 TrimFields: True FirstLineContainsColumnNames: True CustomColumnsSeparator: $''';''' CSVTable=> CSVTable
Variables.FindOrReplaceInDataTable.FindItemInDataTableEverywhere DataTable: CSVTable AllMatches: True ValueToFind: $'''SP''' MatchCase: False MatchEntireCellContents: False DataTableMatches=> DataTableMatches
SET mrRow0 TO DataTableMatches[0][0]
Variables.RetrieveDataTableColumnIntoList DataTable: CSVTable ColumnNameOrIndex: $'''Column#4''' ColumnAsList=> ColumnAsList
SET Price0 TO $'''%''%'''
SET FetchRowAsNumber TO 0
SET ColumnName TO $'''Column#3'''
SET Bool_Valid_Column TO False
LOOP FOREACH CurrentItem IN DataTableMatches
SET FetchRow TO CurrentItem[0]
Text.ToNumber Text: FetchRow Number=> FetchRowAsNumber
IF Bool_Valid_Column = $'''True''' THEN
Display.ShowMessageDialog.ShowMessage Title: $'''Price Details''' Message: CSVTable[FetchRowAsNumber][ColumnName] Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed
END
END

------------------------------------------------------------------------------------------------------------------------
If I've resolved your query, please consider accepting it as the solution and giving it a thumbs up to help others find answers faster.
GregPL 6 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for the hint! After a small modification it helped 😀

Have a nice day mate!