How to use UiPath to extract quarterly survey results from one excel sheet into a separate workbook containing the master data

Help
1

Hi. I am looking for guidance on how to build out an automation in UiPath. Please see the details below:

Two excel sheets:

  1. Survey submission export
  2. Master data file

We receive survey submissions containing financial information on a half-year basis (Q1/Q2 or Q3/Q4) through an internal tool. We then export the data into excel and copy paste the information (based on category and Quarter) into a master excel file.

I have created a template for #1 (above) to leverage in the automation, so that each time we receive a survey submission, we will pull that information in to run in the bot. Since survey submissions are infrequent, I imagine that I would have to complete a few manual steps up front to pull the data into the template file (#1) to prepare in order to run the bot.

2

So you want to seperate the half yearly data to quarterly? @vosstin

3

If you have a quarter name (Q1/Q2/Q3/Q4) in one of the columns in the Survey submission report, then follow these steps.

  1. Use Read Range Activity to read the Survey Submission Report.
  2. Filter the Survey Submission Report Data Table with quarter name.

For example: filteredDataTable = dtSurveySubmissionReport.Select("Quarter='Q1').CopyToDataTable

  1. Use Write Range Activity to write the filtered data table into a master file.

Regards,
Karthik Byggari

1 Like
4

Thank you for your response.

A couple of quick questions for clarity.

The survey results have several categories. Each question is tailored to specific piece of financial data requires a response for each quarter (either Q1/Q2 or Q3/Q4 depending on time of year). The survey tool outputs the quarters as variables “A and B” (i.e., Q1 corresponds to A and Q2 corresponds to B), which are attached to the question (e.g., Leisure Travel (Resorts/Hotels/Airlines) (Q11_A_19). So essentially, you will see each questions replicated twice in the survey output results denoted only by A or B. Would I need to use the Filter DataTable function twice (one for A and one for B)?

5

You can use multiple filter conditions in a single expression as shown below -

filteredDataTable = dtSurveySubmissionReport.Select("Quarter='Q1' OR Quarter ='Q2'").CopyToDataTable

You can add any number of filter conditions using AND/OR.

Regards,
Karthik Byggari

1 Like