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.

[MarqueeCrew]

@SeanAdams ,

Let's give a few words of warnings about Excel and how to ensure good data.  I wouldn't trust Excel with my weekend.  If I'm reading multiple excel files, I could hope that the metadata is compatible between the files.  History has proven that you can't rely upon trust that each file will be consistent.  Even if columns are not changed, it is possible that the data contents might be altered in a way that makes the data type change.

I agree with your calculation of "last", though I would handle it differently.  First, I would use a CReW Data Cleanse (you can use the Alteryx version if you're so inclined) and eliminate empty ROWs.  If there are blank rows at the bottom because someone opened, touched and saved the workbook, you don't want empty rows interfering with real data.  I also skip the SORT of the data by using SAMPLE tool functionality:  Last N Records (N=1) and Group by column (Filename).

So, in the end you have:

DIRECTORY -> DYNAMIC INPUT -> DATA CLEANSE -> SAMPLE

You can use:

INPUT -> DATA CLEANSE -> SAMPLE

But for sleeping purpose, you might want to include a SUMMARIZE to count records from each filename and compare the record count to a DIRECTORY tool pointing to the same data and making sure (TEST tool) that the summarize counts have the same record count as the directory listing.

Cheers,

Mark