Get last row of excel file

[Bhavani_A]

I have around 2000 excel files in a folder. I need one single output file of with the last row of each file. Please note, columns are same in all excel, but number of lines/rows will vary in each excel file.

[ImadZidan]

Hello @Bhavani_A ,

Is there a date field that you can sort on or something to identify that sort to least and most.

[Bhavani_A]

No. There is no date field.

[ImadZidan]

Hello @Bhavani_A ,

Fair enough, I am not sure how you data layout is.

I am attaching an example on how you might do it on a single excel file. This may give you an idea.

If you are willing to share more, we can crack it.

I hope this at least help.

[Qiu]

@Bhavani_A 
I think Dynamic Input and Sample Tool should do the work.

[Bhavani_A]

Unfortunately, i am not able to download the attachment.
Attached sample file of my data. i need last row of this two file in single report.

[pedrodrfaria]

Hi @Bhavani_A 

As @Qiu mentioned, you are looking at a combination of the Dynamic Input, the Formula, the Sample tool.

Use the formula to update the name of the sheets to the correct name, then the dynamic input will allow you to load all those files and the sample tool by grouping by file name and selecting the last row, will allow you to do exactly what you want. 

Pedro.

[Qiu]

@Bhavani_A 

You did not mention it is xls file, which is very tricky and can not get the list of sheets.

-->OK, you did say it is excel file 

So this one will only work if all the sheet name is 'Sheet1'.

[pedrodrfaria]

@Bhavani_A 

I attached the example below that uses a macro to dynamically open all the XLS files. Then as mentioned before, using the Sample to get the last row per file

Pedro.

[SeanAdams]

Hey @Bhavani_A 

I see 4 steps in this:

a) load up all the data:  using the input tool, with the filename set to *.xlsx, and make sure you turn ON the option called "Output filename as field"

- Now you have all rows in; and each row will tell you what file it comes from

b) Add a unique row ID: preferrably per file.   two ways to do this

- Use a multi-row tool

- Use the tile tool - @MattD has a great article here that tells you how to do unique rows: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Tile/ta-p/30235

c) Reverse the order of the data: sort the data by filename and rowID Descending - that way the last row appears first.   

d) Take only the highest RowID for each file: use a sample tool with a group-by on the filename to only return the first row.

that should get you what you need in 4 tools or so.