Import tabbed excel from directory and load data that doesn't start on the first row

[sc6981]

Hi Team,

I have a few xls files in sharepoint which is my source. These are tab separated multiple excel files with valid records from row number 8.

Issue: The files are downloaded into a network directory and 1 tab needs to be read of 3 from record number 8. The file also contains too many records of which I need to filter records which has a specific column value = filename.

Eg: from the below table I need to load from RAG status row (which is column header) and client Account = filename (like Acelity)  

What I need is to have a directory input to read all files, apply some logic to get only 1 tab of 3 tabs from each excel file then read data from the RAG status (row 8) and then apply filter for client Account = filename. Can you please let me know what is the best solution? 

Describe any Account Level or Account Representative Needs:
Red - Closed
Amber - Open, but needs repairs
Green - Open
Property Count:
2476		Property Tracker
RAG Status	Tracker Status (Open or Closed)	Client Account	Property Type	Physical Address
	Open	Acelity	Service Center	2313 W Sam Houston Pkwy N
	Open	Acelity	Service Center	5233 Ih 37

[sc6981]

Hi BarnesK,

Now on running the "Import multiple files.yxmd" there are no records in the output file.

I have one doubt about the macro: Update value and Input data has hardcoded value of Acelity Affected Harvey Locations.xslm however I have 2 files - one for Acelity and other for AFL-CIO Building. If I change the Input data to directory input in the macro then aging I get back the same warning with what I had started.

[Kenda · Accepted solution]

Hello @sc6981. It is okay that within the macro, it uses the Acelity Affected Harvey Locations.xlsm file. This is just for when the macro is being created. When you use the .yxmd file, you are feeding in all of the file locations from the directory tool into the macro. Because it is a batch macro, it will run through each file one at a time. For example, if Acelity Affected Harvey Locations.xlsm is the first record coming out of the directory tool, it will be the first file that is ran through the macro, filtering on client account, and outputting any true values. It will then do the same for the second row that comes out of the directory input tool, until all records (in this case, files) are done. This way, even though you have multiple files, both will get filtered by their unique file name.

I looked at the names in the Client Account field and the reason you are not getting any records is because none of them match your file names exactly and that is what was being filtered on. In order for them to match, you must add a formula within the macro before the filter that gets rid of the 'Affected Harvey Locations' portion of the file name. The expression I used is as follows

left([FileName],FindString([FileName], " Affected"))

This will leave you with just 'Acelity' from the Acelity Affected Harvey Locations file name, which is the same thing that is in the Client Account field. Note, this assumes that all of your files have the same naming convention, which is [Client Account] + 'Affected...'. I reattached the .yxzp file for your convenience. 

[sc6981]

Thank you!!!!