Compare Header names in Excel

Learn Academic Alliance
1

Hi Team,

I need to check that the the excel received in sharepoint should always contain the same header names stored in collection and if there is any discrepancy then BOT will throw the exception and send an email in the below format :
Hello,

The headers within the Initial User List, “[FILE NAME]” do not follow the correct format. Please update the headers based on the information below and replace the file to be validated once again, prior to ingestion.

Current Header Format:

[Paste existing incorrect Headers here in a table format like the below]

Correct Header Format:

CAMPAIGN NAME USER NT LOGIN USER FULL NAME USER EMAIL (IF APPLICABLE) RES1 RES2 RES3 APPROVER NT LOGIN APPROVER FULL NAME APPROVER EMAIL

Thank you.

2

@som17 If you are able to download the Excel File from the Sharepoint Site and the Excel is stored in your local machine, you can then Access the File and use Read Range Activity to read the Excel as a Datatable and then we can perform the Comparison operation between the Columns in the Excel and the Collection you have defined. But is it necessary that the Column Order be the same as well?

3

yes…Column Order has to be same always.

4

Hey…i did it…Built up the logic and it worked…thanks :slight_smile:

1 Like
5

@som17 Good Going :smiley:

1 Like
6

can u help me as how can i append table in the body of email…below is the table to be appended…
CAMPAIGN NAME USER NT LOGIN USER FULL NAME USER EMAIL (IF APPLICABLE) RES1 RES2 RES3 APPROVER NT LOGIN APPROVER FULL NAME APPROVER EMAIL

Pic2
Pic21085×214 5.76 KB

7

@som17 If you have that table data as a Datatable, then you can this Activity to convert the Datatable to Html Table and use the Output in the Body of the Mail that you want to send with IsBodyHtml as True.

8

actually i just need to capture the sequence of headers from the excel file received from Business and send them in table format to show them the discrepancy
Plus share the correct format of sending the file with correct header names like above image.

9

@som17 Yes, If you need the headers to be shown in the Table Format then you would ultimately need to convert it to an HTML table, and then you can send it in the mail body. Otherwise it would not be such an easy solution.

12

Good morning @som17. What steps (approach) did you take to retrieve the header row content and evaluate the header values?