Add data row where an excel with Table Design

Help Studio
1

Hi there,

I got an excel with 2 header rows and a table design. I am trying to use add data row activity for the first 6 columns and then write it to A3. How do i go about it?

2

Hi @mark_rajkumar1

Your query is quite confusing. Could you be more specific.

3

Hi @mark_rajkumar1 ,

  1. Read the Excel File:
  • Use the Read Range activity to read the data from the Excel file. Make sure to specify the correct range to include the header rows.
  1. Manipulate the DataTable:
  • Use a For Each Row activity to loop through the data and prepare the new rows to be added.
  • Use the Add Data Row activity to create and add rows to a new DataTable.
  1. Write the Data to Excel Starting from A3:
  • Use the Write Range activity to write the data starting from cell A3.

Example Workflow:

Step 1: Read the Excel File

  1. Add an Excel Application Scope activity and provide the path to your Excel file.
  2. Inside the Excel Application Scope, use the Read Range activity to read the entire data including the headers. Set the range to something like "A1:F100" (adjust the range as necessary).
<Excel Application Scope WorkbookPath="path_to_your_excel_file">
    <Read Range Range="A1:F100" Output="dataTable" />
</Excel Application Scope>

Step 2: Manipulate the DataTable

  1. Create a new DataTable variable (e.g., newDataTable) to hold the manipulated data.
  2. Use a For Each Row activity to loop through the dataTable.
  3. Inside the loop, use an Assign activity to create a new DataRow.
  4. Use the Add Data Row activity to add the new row to newDataTable.
<For Each Row in DataTable dataTable>
    <Assign>
        newRow = newDataTable.NewRow()
        newRow("Column1") = row("Column1")
        newRow("Column2") = row("Column2")
        newRow("Column3") = row("Column3")
        newRow("Column4") = row("Column4")
        newRow("Column5") = row("Column5")
        newRow("Column6") = row("Column6")
    </Assign>
    <Add Data Row DataTable="newDataTable" DataRow="newRow" />
</For Each Row>

Step 3: Write the Data to Excel Starting from A3

  1. Use the Write Range activity to write the newDataTable to the Excel file starting from cell A3.
<Excel Application Scope WorkbookPath="path_to_your_excel_file">
    <Write Range SheetName="Sheet1" Range="A3" DataTable="newDataTable" AddHeaders="True" />
</Excel Application Scope>

Regards
Sandy