Using Alteryx-Python , generate output excel file in attached format from the input file

[sanky1990]

Hii

1) Looking for Alteryx workflow or python code assistance for converting input excel file into output file in attached format. 

  'Notes' column is the 1 where i need to break cell value into multiple rows (each starting with date:) and also new column with only date.  Then other date columns there are some format changes required  and then finally a merge for each of other columns as per given in output file, 

(Need to make sure output file is generated in same formatting as attached output file)

Attached files:

excel input file: Input_to_python.xlsx

excel output required:  Expected_output.xlsx

2) Also looking for reverse logic (alteryx or python or both), meaning if my input file is 'Expected_output.xlsx'  and i have to now generate output file as Input_to_python.xlsx

[Yoshiro_Fujimori]

Hi @sanky1990 

Splitting a cell with multiple lines can be achieved with one [Text To Column] tool with [Split to rows] option as below.

However, [Expected_output.xlsx] contains merged cells, which is not easy to achieve on Alteryx (or even on Python),

because the data is "unnormalized".

If the output is to be used for further analysis, I would keep it as "First normal form".

As a bonus, your work to "reverse" the data format would be much easier.

If you still have to comply with the original format with merged cells,
I think it would be better using VBA (though I am not familiar with it).

[sanky1990]

Hii @Yoshiro_Fujimori 

Thanks for the response. unfortunately given unnormalized excel report is what i need to generate and automate for stakeholders.  I am exploring vba and other options

Any inputs for the other way around like you mentioned "reverse"  the data format that you can help with?

[Yoshiro_Fujimori]

If you need to revert to the exact format of the original Excel data, you would need to use VBA anyway.

As I do not dare to tap into VBA, I cannot help further.

As an idea to reduce the pain, you may want to keep the data in 1NF before you convert to your desired format.

Then you can use the 1NF data to reverse it similar to the original format.

Unfortunately, Alteryx cannot deal with data containing merged cells.

[sanky1990]

Any example that i can reference for doing this approach of 1 NF? i am not familiar with it