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

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

Common Use Cases

Accepted answers

Kenda

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.

Import Multiple Files.yxzp

All comments

andre347

You could do this with a batch macro. Would you mind posting some dummy Excel data/files so I can create a mockup?

Andre

Kenda

@sc6981 I am not familiar with sharepoint, but based on the data and details you provided, I think I might be able to help.

As shown below, I just used a normal Input Data tool, but used an asterisk in place of a file name. This will enable you to input all of the files from that folder location that end in .xls. Assuming all of your data will have the same sheet name in all of the workbooks, that is chosen here as well. In row 5 of the configuration, you will want to choose 'File Name Only' in the drop down so that you can keep each of the file names as a field in your data.

Import Multiple xls Files.PNG

I then used a Sample tool to get skip the first 6 rows then used the Dynamic Rename to change the column names to the first row of data (check all of the fields except FileName). From there, you can use a simple Filter tool to keep only the Client Accounts that equal the FileName field. Lastly, you can add a Select tool to get rid of this FileName field.

Import Multiple xls Files Output.PNG

Hope this is helpful!

sc6981

Hi,

I did try a similar approach but was getting the below error while parsing the second file:

Warning: Input Data (7): The file "C:\RED Projects\Hurricane Harvey Files\Acelity Affected Harvey Locations.xlsm|||`Property Tracker - Survey$`" has a different schema than the 1st file in the set and will be skipped

On searching the net I understood that I need to use Directory Input and not Input Data (although I am not sure why).

I am attaching the mapping with two sample files. Can you please help?

New Workflow2.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703