hi,
I have an excel with headers and subheaders, like the below:
I have to move to the next header only after making entries for the first set of rows (first heading + subheading) in SAP.
Could you please let me know how to differentiate between the set ? I have attached 2 sets for example.
Do you have 5 to 7 sets? We’ll use this for analysis.
sample.xlsx (11.0 KB)
there will be several sets, but I have created sample 5 sets for reference.
Hi @Aishwarya ,
Thanks for sharing the sets for analysis. There is a repeating pattern for reading the sets and I hope this would help you
Use Excel Read Range where Input Range is
first set - A1:K7 or “A1:K” + (CellRowNumber - 2).ToString
second set - A9:K19 or “A” + CellRowNumber.ToString + “:K” + (CellRowNumber - 2).ToString
third set - A21:K27 or “A” + CellRowNumber.ToString + “:K” + (CellRowNumber - 2).ToString
fourth set - A29:K35 or “A” + CellRowNumber.ToString + “:K” + (CellRowNumber - 2).ToString
Where CellRowNumber is 9,21,29,etc. omit 1 because default is 1.
Create a variable to hold a List of Integers: ListOfNumbers = {9,21,29,…} and put inside For Each activity
Inside For Each activity read each item where item is the CellRowNumber and passed the first set, second set, etc.
To get the list of numbers from Excel, you have to insert a new column into Excel - for example column L the excel formula:
=IFERROR(MID(IF(CELL(“contents”,A1)=“Number”,CELL(“address”,A1),""),FIND("$",IF(CELL(“contents”,A1)=“Number”,CELL(“address”,A1),""),2)+1,5),"")
Which will give you a column of numbers and blanks - Column L
Filter on Column L to remove blanks.
Copy and the entire column L [shift+ctrl+down arrow], paste Values(V) to new sheet
Transpose
Copy to Notepad and Replace tab to commas
Now you can copy the string into Assign activity ListOfNumbers = {9,21,29,37}
But there would be different excel sheets every time with different rows of data under each heading. The sets wouldn’t fall under the same row numbers always.
Are we hardcoding the cell range in the excel read range ?
Oh, I see.
Unfortunately, yes, it is hardcoded but you did not mention different Excel sheets. The initial analysis might becomes invalid.
But, I think you can try with my suggestion and based on that
Ohh, My apologies, this is for a project that needs to run different sets of data in an excel. Each time the data from an excel has been moved to SAP, there would be another excel which needs to be read and insert data into SAP.
Could you please let me know if its possible without hardcoding the range?
At the moment, this only thing we can think of, please try this workflow to automatically extract the ListOfNumbers
Properties
Thank you so much. I’ll try this out.
Hi,
Can we move data to SAP from a data table or SQL ?
Or can we do it only using Excel and script recording and playback?
Regards,
Aishwarya S
Script Recording and playback can always read from excel only or we can do it from SQL and datatable too?
We haven’t tried that yet, only check from excel.
This topic was automatically closed 60 minutes after the last reply. New replies are no longer allowed.
