6/17/21: We have completed maintenance for the Search functionality on the Community. If you are seeing any issues, please try to clear your cache first. If the issue persists please email Community@alteryx.com

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Indented excel file input

djohn
8 - Asteroid
Hi
I am unable to keep the indentation on the excel file I am inputting to a workflow.
Based on the indentation, I need to classify the fields and label them. Is there a way to identify the data by the indentation in Alteryx ? The image attached shows the data in excel. They will be labeled as Divison, Base and Parent respectively. The furthest indent is Division followed by Base and Parent.
21 REPLIES 21
djohn
8 - Asteroid

Hi Joe,

Thank for the macros. I tired running them but ran into errors. I renamed the input As 'Sample file.xlsx' and  have only one sheet name 'Sheet1'. Is that causing the issue? Do I need to modify the input in the other 2 macros?  The errors are:

 

 Read XLSX Cell Formatting (10) Record #1: Tool #51: Record #1: Tool #10: File not found "C:\Users\djohn\Desktop\Macro Test for Emerson\C:\Users\djohn\Desktop\Macro Test for Emerson\Sample file.xlsx"
 Read XLSX Cell Formatting (10) Record #1: Tool #51: The output connection "Output16" was not valid

 

 

The desired output would have = Base Division Parent = all in one row. I'm attaching a ample for your reference. 
Could you please help me figure out what went wrong?

 

Joe_Mako
12 - Quasar

I am not able to recreate the error. Does it work with the provided sample file? or does it also fail?

 

I am not able to align that desired output with the sample file provided.

 

What is the desired output form the sample file provided:

 

in.png

djohn
8 - Asteroid

Hi Joe,

I used the Sample file attached here and its fails.

The desired output would be obtained by using the Hierarchy table. Please find attached here for your reference. I am sorry, this post originally didn't include the Hierarchy table.

The label column in Hierarchy corresponds to the first part og the first column in Sample file. We want to classify the first column in Sample file as Base Division and Parent using the Hierarchy table.

 

For now I have no identifier to associate 'Base' to which division or which parent. The desired output of the sample file is attached as well. Thanks much for your time and help.

 

djohn
8 - Asteroid
  1. hierarchy table
djohn
8 - Asteroid

1. Sample file

Joe_Mako
12 - Quasar

Okay, that makes sens now. I found my bug, I was using the wrong sheet ID from the XML, I have resolved that in the attached files, and added to the workflow to reshape the data.

 

My logic is if the row is not used as a parent for another row, then it is considered a Base. I then added columns for the Parent (1 level up) and Division (2 levels up). I have also attached the results.

 

base parent division.png

 

Please let me know if you would like meet for a screen share to walk through these workflows and macros together, or if I can help in any other way.

djohn
8 - Asteroid

Great that is what I was looking for! Thanks a ton!

I replaced the Input with the original file but it dint work. Does it have to be in a different format? I changed the input values in the 2 sub macros and gave me errors as well.

PFA the error screenshots.

Joe_Mako
12 - Quasar

I would like to help you, I have Webex open, please let me know.

 

Editing the macros will not work, unless you connect as a zip file and read in the correct file as XML, it is a tricky input configuration.

djohn
8 - Asteroid

Thanks for the support, Joe.

Would the macro work for input files that has different headers?

I tried placing 3 files in the input folder and each file has a different headers. So on the select tool I was hoping to view all the headers but it doesn't show up. Is it because the data are stacked and the header rows are part of the data values now? Hope its not to confusing

Joe_Mako
12 - Quasar

Use the settings "with Columns no header row" and then use other tools like Filter and Dynamic Rename to adjust the data so it is cleaned up. Another option is to use "Transpose" and then tools like Filter and Cross Tab. There are many routes, it all depends on your situation, the macro I made is flexible, you just need to add some tools after it to do exactly what you want with your data..

 

If you have trouble please provide sample before and after data, thank you!

Labels