Write data in excel

Help StudioX
,
1

I have to automate a web process, where I was registering a form using entries from an excel file. after submitting the form it gives Unique ID and If the details are duplicate then gives message as “Duplicate entry found” and doesn’t generate unique ID. I want to store Unique ID or Duplicate Entry in the Excel sheet new column in respective rows. Please help me how can I write entries in the respective column using for each loop in excel activity

2

Hi @sgarg

  1. Use the “Excel Application Scope” activity to open the Excel file.
  2. Within the “Excel Application Scope”, use the “Read Range” activity to read the data from the Excel sheet into a DataTable variable. Assign this DataTable to a variable, let’s call it dtData.
  3. Use a “For Each Row” activity to iterate over each row in the dtData DataTable variable.
  4. Inside the loop, access the data from each row and perform the web automation process to register the form.
  5. After submitting the form, retrieve the Unique ID or the message indicating a duplicate entry using get text activity.
  6. Use an “Assign” activity to assign the Unique ID or the duplicate entry message to a variable, let’s call it entryStatus.
  7. Use the “Assign” activity to update the value in the respective column of the DataTable variable.
    For example, if the Unique ID or duplicate entry message needs to be written in column C, you
    row(“C”) = entryStatus
    After the loop completes, use the “Write Range” activity to write the updated DataTable back to the Excel sheet.

Hope it helps!!

3

Hi @sgarg
Use Below Steps to achieve this automation.

  1. Use the “Read Range” Workbook activity to read the Excel sheet data and save it to DataTable type variable. DT_Excel_Read
  2. Use “For Each Row” activity & pass DataTable variable DT_Excel_Read.
  3. Inside the loop, process all the steps for registering into the Browser.
  4. After those steps ,Use “Get Text” Activity for extracting Unique Id or the Duplicate message and save to a variable of type string lv_GetStatus.
  5. Now, Use the “Assign” activity & pass lv_GetStatus Variable in the Set Value place and in To Value pass Row(“Column_Name”).
    Row(“Column_Name”)= lv_GetStatus
  6. After Loop completes Use the “Write Range” activity to write the DT_Excel_Read DataTable back to the ExcelFile.

Hope this works !!

4

Thanks for your prompt reply. But I couldn’t get how can I use same variable for two different Get Text Activity. In 1st Get Text Activity I am extracting Unique ID and storing that in a variable let say varID How can I use same variable to store the message if unique ID is not generated.

Please Help

5

Use the first Get Text activity to set varID to the Unique ID.
Then add an If condition: if varID is Nothing or an empty String, then use the 2nd Get Text activity to set varId to store the message that says that the Unique ID was not generated.

6

@sgarg

  1. Use the 1st Get Text activity for variable varID to extract Unique ID.
  2. Then use If condition, in condition type varID is Nothing or varID = “”, then use the 2nd Get Text activity for extracting the message that says : Unique ID is not generated.

Hope this helps!!

7

Thanks everyone for your help. It worked perfectly :slight_smile:

8

@sgarg

If you got your Answer then please mark it as Solution.

9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.