Taking input from excel and and write data into same excel

Help
1

hii guys,the question might be a silly but suddenly got struck.i have a excel sheet with 4 columns say url,name,id,number.out of which only url column contains data remaining columns null value.so the thing is i have to take data from the url column and open browser and need to get the data of remaining columns and write the data into that columns.
-i could able to do till opening browser and getting data.struck while writing data into columns name,id,number.
-the url column contains 10 rows.so for each row it has to take url from that and get the data of columns and write into the excel.plz help me out.
@Palaniyappan @lakshman

1 Like
2

@venkatmalla6

  1. use Read Range Activity to read the Data from Excel and store output in dataTable and say DT.

  2. Then use ForEach row Activity to iterate that DataTable.

ForEach row in DT
Int index = DT.rows.IndexOf(row)+2
Use write cell Activity to write the data into corresponding field.
If it is B- Column then range should be like this:
“B”+index.Tostring

3

Follow these steps -

  1. Excel Application Scope (Check - Autosave, Uncheck - New file & Read-Only in the properties)

  2. Read Range only for column A, give address as “A:A”. Output is saved to Data table say dtValues

  3. Declare and initialise one variable, say intCounter = 0 (if your excel contains headers set default value to 1)

  4. For each row in dtValues
    intCounter = intCounter + 1
    strURL = row(0).ToString
    Open Browser sequence and get the values into three variables strName, strId, strNumber
    //Use Write Cell Activity to write the output values into Excel File
    Write Cell Activity, Value = strName ; Address "B" + intCounter.ToString
    Write Cell Activity, Value = strId ; Address "C" + intCounter.ToString
    Write Cell Activity, Value = strNumber ; Address "D" + intCounter.ToString
    Next

Regards,
Karthik Byggari

4

@lakshman @KarthikByggari as you can see my xaml and excel file i did same thing but its writing data somewhere.plz have a look at my fileNew folder (3).zip (8.8 KB) eagerly waiting for reply.thankyou

5

@lakshman @KarthikByggari sorry don’t think in any manner waiting for your reply. hope you people seen my attachment.

6

Sorry @venkatmalla6 for delay.

As I can see in your write cell activity, you gave address as “B1” + “Counter”.ToString

But it should be “B” + “Counter”.ToString

Similarly for another write cell, give “C” instead of “C1”.

Regards,
Karthik Byggari

7

Fine
Hope these steps could help you resolve this
—use a excel application scope and pass the file path as input
— inside this scope use a read range activity and get the output with a variable of type datatable named outdt
— now use a for each row loop and pass the above variable as input
— inside this loop use a open browser and mention the url you have
— within this use a type into activity and mention as row(0).Tostring as the first column has the data for url
— hope you have got the value of each column in each variable
—here is your answer
See still we are inside the loop with the first under iteration only now we can assign the value to the respective columns of that first row
Like this with simple assign activity
row(1) = invalue1.ToString
Next assign activity
row(2) = invalue2.ToString
Next assign activity
row(3) = invalue3.ToString

Simple
— now next use a kill process with process name as “iexplore” atlast to close the browser (INTERNET EXPLORER) and open again as we are inside the loop
So every loop corresponding column of the current row will be assigned with the value

Simple isn’t it
Hope this would help you
Cheers @venkatmalla6

1 Like
9

@KarthikByggari Capture20 it’s written as expected but it is not writing the product name.this is the selector for my product name title for both the urls which are present in excel sheet.when i used this selector it is writing product name for each url but inside the loop only one product name is writing for both the urls.

10

Were we able to insert data to those columns
@venkatmalla6

11

@Palaniyappan I’m using workbook range activities not excel activities and I used as counter variable to write data into cells it written but the product name is not writing.

  • say now I have morethan 10columns to write data in it,so for every cell rather than using write cell or assign activity do we have any alteranative solution to write data into it.
1 Like
12

@venkatmalla6

assign values like this:

row(“name”) = “name Value”
row(“id”) = “id value”
row(“number”) = “number value”

And then finally use Write Range activity to write into excel file.

13

That’s a good question
—fine when we have more than ten columns
Create a new datatable by using a simple assign activity
*Outnewtable = yourolddatatable.Clone
Where Outnewtable is a variable of type datatable with default value as new System.Data.Datatable
—now we can use a add datarow activity with arrayrow property passed as input
Like in arrayrow
{row(0).Tostring,value1.ToString,value2.ToString,value3.ToString,…}

Where value1,value2 are obtained from the web application and can be passed as data to the datatable via add datarow with this arrayrow property and datatable name as inpu

Hope this would help you
Kindly try this and let know for any queries or clarification

And for this

Better use columnname explicitly instead of columnindex
row(“yourcolumnname”) = value1.ToString
row(“yourcolumnname”) = value2.ToString
… ** goes on**

Cheers @venkatmalla6