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

[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. 

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.

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? 

[sc6981]

+ xls

[sc6981]

Any help on this??

[Kenda]

Hey @sc6981, not sure why the Input tool wasn't working. To me, it looks like they have the same schema. However, I was able to create a simple macro that should help you out. I attached it to this post. 

Simply put your Directory tool onto the canvas then use a Formula tool with the following expression: 

[FullPath] + "|||'Property Tracker - Survey$'"

This will append the sheet name to the file path. From here, you will connect the macro and choose FullPath as the chosen field. Hope this helps!

[sc6981]

Hi,

I tried but it is still not working

[sc6981]

Please help

[Kenda]

Hello @sc6981. I attached a workflow where I utilized the macro I created. You will want to import everything in the .yxzp file. Note I am using the macro like a normal tool from the palette. It looks as though you were trying to build your workflow within the macro. Try using the workflow that I attached and hopefully that will make more sense.

If you want to learn more about macros, here is a link to a webpage on the Alteryx website: https://help.alteryx.com/current/Macro_Modules.htm