cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
knightmetal
Regular Visitor

Extract data from webpage as a variable WITH a non-standard delimiter

I'm scraping data from a table and passing it to a variable so it can later be processed. The issue is that the rows/strings use commas to separate each element (the only available option from what I see), but some of the individual elements in my dataset also use commas whenever they contain multiple values. As a result, my data is getting messed up when I use 'Split text' to manipulate each of the elements in my table as I'm getting a different number of values in each row. For example:

 

Jon, 19, Law, New York, football, basketball, baseball 

Mary, 22, Accounting, tennis

Peter, 20, Engineering, ping pong, running

 

The sports should be part of one value, meaning I should have 4 values in each row. PAD is giving 7 values in row 1, 4 values in row 2 and 5 values in row 3. That's a problem. Is there any workaround for this when using variables? As a worst-case scenario I guess I'd have to save the data in Excel, change the delimiter there and then pass the data back to PAD but that's crazy considering how handy variables can be directly in PAD. I'm surprised we can't change the delimiter for variables? Thanks a lot!

2 REPLIES 2
Siddhi_N
Frequent Visitor

Hi @knightmetal 

 

1. set a variable named  table and initialize it with %{ ^['Column1', 'Column2', 'Column3', 'Column4'] }%

2.Create a list named newlist

3.split the extracted data from web using delimiter as newline consider o/p variable list1

------loop1--------

4.loop1 through this list1 from start-0 and end-%list1.Count-1%

5.inside this loop1 we want 1 row from list1 so b with parameter as %list1[loopindex1]% and delimiter as ',' o/p variable list2

----loop2---

6.Loop2 through this list2 from start-3 and end-%list2.Count-1%

7.Inside this loop2 drag 'add to list' action and give parameter into list-newlist, add item-%list2[loopIndex2]%

----end loop2---

8. Join variable newlist with delimiter as none , o/p variable achieved JoinedText

9.set variable table as %table + {[list2[0], list2[1], list2[2], JoinedText] }%

10.drag action 'Clear List' with parameter- newlist

----end loop1----

(Note-considering expecting result col1-Jon, col2-19,col3-Law,col4-New York, football, basketball, baseball)

Hope this solution helps!

knightmetal
Regular Visitor

Thanks a lot for your detailed reply, Siddhi_N. Last night after I wrote my message I actually came up with a solution that seems to be working (so far so good). I'm sharing it here to help the community.

In the end I decided to use the 'Extract data from web page' action three times separately. The first time I group all columns which I know will always have individual values (with no commas). The other two times I extract data from two columns (the ones giving me trouble yesterday) which might have a few commas and even parapraphs here and there. Then I use a 'For each' loop to iterate over the first group of data, and inside the loop I use a counter to fetch the values of the other two groups. Like this (I'm using a 'Display message' Action just to ilustrate the point):

For each 'CurrentItem' in 'DataFromWebPage1':

Display message:
%CurrentItem%
%DataFromWebPage2[Counter]%
%DataFromWebPage3[Counter]%

One clarification in my first message is that the word New York wasn't supposed ot be in that table.

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,524)