How to loop through excel files which has an image header followed by data, and store only few columns in variables and lists using querying?


#1

I have 3 excel reports which needs to be iterated for some information but they contain an image header followed by data. I need to use an outer loop and 2 inner loops so as to merge the 3 excel files and store the variables in a list or string for future use. What I need is first go through each person in Patron_information_detail_ report, now create an inner loop which has outstanding bill reports. using the barcode of the person, get the fiscal fine outstanding bill and save in a variable, with the second inner loop which is all outstanding reports I need the list of books each person loaned and add them into a list of books. Help me with this workflow.

Gist of querying for merging reports. Don’t use item barcode its only patron bar code:
For student in detail_report {

student_books.clear()

student_fines = 0 For book in books.where(book.barcode = student.barcode {

    student_books.add(book);

}    For fine in fines.where(fine.barcode = student.barcode {

    student_fines = student_fines + fine;

}      PrepareEmail(student, student_fines, student_books)

}

Finally the result should be like this.
Hi Montgomery,

You have an outstanding balance of $256.50 at the Davidson College Library.
Item Title Reason Date Balance
N/A ILL Overdue 02/23/2012 $1.00
O brother, where art thou? OVERDUE 02/26/2012 $5.25
N/A ILL Overdue 04/05/2012 $3.00
N/A ILL Overdue 04/16/2012 $15.00
The passionate fictions of Eliza Haywood : essays on her life and work OVERDUE 05/01/2012 $18.00
Beyond spectacle : Eliza Haywood’s female spectators OVERDUE 05/01/2012 $18.00
Edna Pontellier and Lily Bart : a study of character and early criticism in Kate Chopin’s The awakening and Edith Wharton’s The house of mirth OVERDUE 05/13/2012 $0.50
A historical guide to Edith Wharton OVERDUE 05/14/2012 $0.75
Edith Wharton’s The house of mirth : a casebook OVERDUE 05/14/2012 $0.75
La Celestina OVERDUE 05/14/2012 $25.00
Memorial boxes and guarded interiors : Edith Wharton and material culture OVERDUE 05/14/2012 $0.75
Del libro del conde Lucanor et de Patronio OVERDUE 05/14/2012 $25.00
Calila e Dimna OVERDUE 05/14/2012 $25.00
Spanish second language acquisition : state of the science OVERDUE 05/14/2012 $25.00
The art of teaching Spanish : second language acquisition from research to praxis OVERDUE 05/14/2012 $17.00
La Celestina OVERDUE 05/14/2012 $25.00
Edith Wharton and the making of fashion OVERDUE 05/14/2012 $0.75
Ca?rcel de amor OVERDUE 05/14/2012 $25.00
Displaying women : spectacles of leisure in Edith Wharton’s New York OVERDUE 05/14/2012 $0.75
Amadi?s de Gaula, novela de caballeri?as, OVERDUE 05/14/2012 $25.00

Total: $256.50

Forget about the reason. The most important variables I need to store are First+last name, list of books rented, fine. I can get the remaining fields later on after seeing the workflow. Kindly help me out with this.
Outstanding_Fees_and_Bills_Report (3).xlsx (533.0 KB)
All_Checked_Out_Items_Report (3).xlsx (566.6 KB)
Patron_Information_Detail_Report (2).xlsx (317.2 KB)


#2

@vvaidya @aksh1yadav @andrzej.kniola, Can you help me build a workflow like that?


#3

I used read range activity from the work book, and gave the range after the image header, the first part is solved. Next part I am not able to proceed forward. Can anyone help me with this.