How to extract format information from an excel/xml file

ignacio_oliva

Hello,

I have an XML file that I also use with Excel. The Excel/xml file serves as a template with multiple sheets, each with a table. Each column in a table represents a field (displayed in the first row), and the color in which that field is filled determines whether it's a mandatory or optional field(orange-colored fields are mandatory, while grey-colored fields are optional). I would like to know how I can extract this color information using Alteryx (maybe extraction RGB format?). Can anyone assist? 

Thank you!

 

BS_THE_ANALYST

@ignacio_oliva 

You'll need to unzip your .xlsx file.

This will reveal the underlying composition of that .xlsx file which, as you mentioned, is composed on .xml files.

You'll find .xml files which will be of interest. 1 one will be the called something like "SheetName".xml i.e. Sheet1.xml & you'll also have another sheet called styles.xml.

Sheet1.xml will reveal the contents on the page and you'll find an attribute called "S=NUMBERHERE". I think this is the lookup to the Style sheet. Then you can go to the Style sheet and find out the colours and fills etc. I'd use chatbotgpt to build that part of the logic for you so you can step through it.

Couple of questions from me. 

1. Where does alteryx sit in the process? 
   1. I.e. are you trying to use Python (within alteryx) to automatically unzip the .xlsx file and then scan the appropriate files and rip out the needed info? 
2. Are you trying to automate this or just do a one-off?

Let me know how you get on.

All the best,

BS