Transpose of data

Hello guys ,

Need a small help , suggestion , i struggle with a process ,

i am scraping a data table in the mid of a scraping process, for example link ,

https://ec.europa.eu/taxation_customs/dds2/ebti/ebti_details.jsp?Lang=en&selectedReference=&reference=DEBTI1132/20-1&refcountry=&valstartdate=&valstartdateto=&valenddate=&valenddateto=&suppldate=&nomenc=&nomencto=&keywordsearch=&excludekeywordsearch=&descript=&orderby=0&totalRecords=138197&linkVal=&viewVal=&keywordmatchrule=OR&specialkeyword=&excludespecialkeyword=

Please see the table in attached screenshot ,i want to transpose that table only column 0 and 1 enough , if you can suggest to take the image in last in excel cell in similar way that is plus, but at least suggest the transpose,

although i tried with Anchor base but due to variable size of cells and data , selectors i am not able to tune whenever data in particular cell is les
Capturescreen
searchonline.xaml (54.9 KB)

I attached the work flow as well excel file from which i take the data and search online ,then try to scrap the data, take the data and need to put in excel.

Anyone if can give 15 min to look at would be great,Test data.xlsx (15.2 KB)

Search link is here ,

https://ec.europa.eu/taxation_customs/dds2/ebti/ebti_consultation.jsp?Lang=en&Lang=en&refcountry=&reference=&valstartdate=&valstartdateto=&valenddate=&valenddateto=&suppldate=&nomenc=&nomencto=&keywordsearch1=&keywordsearch=&specialkeyword=&keywordmatchrule=OR&descript=&orderby=0&Expand=true&offset=1&viewVal=&isVisitedRef=false&allRecords=0&showProgressBar=true

Hi @Vinay_Swarnkar,

Hope below solution helps!

Step1 - Scrape the datatable from the site and store it in datatable variable.

Step2 - Use custom activity to transpose the above datatable. (You may use NovigoSolutions.TransposeDatatable.Activities) You can get this package installed from Manage Packages to get this custom activity available in your Activities panel.

Cheers!
Lipika

1 Like

Can you suggest how to scrap data table from the already opened Page. There will be 1000 times loop each time different page.

@Vinay_Swarnkar,

Does the webpage have a next or similar button which takes to the next pages from where the data has to be extracted.

If yes, then use the DataScraping feature to scrape the data, and then youll be asked if the data is spanning across multiple pages. Click yes and indicate the button which takes to the next pages.(Screenshot attached)

@Vinay_Swarnkar

Were you able to extract the data from multiple pages, if not, you may refer below video which has detailed steps.

Hey

thanks for the suggestions ,

I have tried these before , then came to anchor .

please refer below URLs,

first we need to click onfirst element then go inside then scrap details .

if you directly click on next , after 30 click it ill bl0ck you and repeat the same for 1000 times

https://ec.europa.eu/taxation_customs/dds2/ebti/ebti_consultation.jsp?Lang=en&Lang=en&refcountry=&reference=&valstartdate1=&valstartdate=&valstartdateto1=&valstartdateto=&valenddate1=&valenddate=&valenddateto1=&valenddateto=&suppldate1=&suppldate=&nomenc=392690&nomencto=&keywordsearch1=&keywordsearch=&specialkeyword=&keywordmatchrule=OR&excludekeywordsearch1=&excludekeywordsearch=&excludespecialkeyword=&descript=&orderby=0&Expand=true&offset=1&viewVal=&isVisitedRef=false&allRecords=0&showProgressBar=true

ExtractData.zip (128.5 KB)

Hi @Vinay_Swarnkar

Please find attached the xaml i have created on the given website. It extracts all the 4000+ records. The excel file contains all the records extracted by the bot.

Do try and let me know how it goes.

Regards,
Lipika Panda

1 Like

Hi Lipika ,

Thanks,

This table is outer table , there is data inside every line item that need to be scraped , and there are 1400000 line items not just 4K.

This step i already done month ago , problem is data inside this.

Best regards

@Vinay_Swarnkar Could you please be more clear by sharing the screenshot of table from which information has to be scraped and which all fields.

Hello ,
Thank you for your reply ,

Please click on the line item i high lighted in the screen shot , you will be taken to a page that data need to be scrap in excel . including picture at cell .

Please refer the screen shot and my process (experimental).

I am able to pic the data but not able to write in excel if you can help in that ,try out.
https://ec.europa.eu/taxation_customs/dds2/ebti/ebti_consultation.jsp?Lang=en&Lang=en&refcountry=&reference=&valstartdate=&valstartdateto=&valenddate=&valenddateto=&suppldate=&nomenc=&nomencto=&keywordsearch1=&keywordsearch=&specialkeyword=&keywordmatchrule=OR&descript=&orderby=0&Expand=true&offset=1&viewVal=&isVisitedRef=false&allRecords=0&showProgressBar=true
My code is only writing 2 columns and 1 line while it runs the code for 10 line items from sample file and writing only 1 raw and 2 columns.https://aws1.discourse-cdn.com/uipath/original/3X/c/b/cb0ba181579963af173010509678042466c6ea31.png

I have attached the flow although it is a rough but , it is taking a data from file search online and then write back to excel by performing scraping,

Last 5th column is like data table how can i get this data in a single cell as string,

although by message box activity i am able to see this is being scraped well only not being written in excel.

scrap is output file and test data file from where i take the details.
4beec0d9afdd212a379f2084eeb7a863c6da98a2.png
below is link where is search and get the details.
3iWXQZGZzmIYFONK4BDC6vnpEg3.xlsx
searchonline.xaml (68.7 KB)

Hi @Vinay_Swarnkar

You may use an assign activity to format the text extracted in the extracted data table cell before writing it to excel so that it stays in the same cell and doesnt flow to subsequent one’s.

ExtractDataTable(12)(“Field Values”) = Join(Split(ExtractDataTable(12)(“Field Values”).ToString))

I have attached the xaml file for your reference.ExtractData.zip (12.2 KB)

Thanks!
Lipika

1 Like

Hi ,

Thank you so much for your regular help and interest.

I will try and revert you.

regards