[Python] .txt File Transforming in Python or PowerBI [closed]

I am currently working on a project and have a .txt. file that I need to transform as the data is not properly structured and am unsure how to proceed. Currently, the file contains all data with headers and data in rows with different delimiters.

I have included a sample of the file as an image. The fields within the red squares are required for AccessIdentifier, I only need the numbers following "MG:" or "CP:" but have AccessIdentifier as a column with the "MG:" or "CP:" numbers in the row (I don't need both, just MG if CP is not listed.

UserName is also a column header and would need all the characters on that line after the colon in rows, "mail" is another column (emails). I would need to place as a header and all the data within that line after the colon in rows.

Lastly, the "portals" would also need to be a column header with all data in that line after the colon in rows and must contain port 52. text file

As you can see the text file has the headers repeat on every line with the data following the colon. This would be much simpler if there were not additional colons in some of the lines (for example, in the image AccessIdentifier) has various colons and semi-colons.

the "dn" column can be included but can also be dropped and would include all data within that line in rows and exclude the data after the first comma. I have tried researching a resolution with no avail. Not sure if it is an issue that has not been encountered or if my search criteria is incorrect. Either way, any assistance is greatly appreciated. .txt file

I tried a few different methods within PowerBI, searched Google, reviewed over 40 site responses found in searching the web, do not have Anaconda installed on my laptop but will have it soon and am simply a beginner with python but believe that would be my best option for what I require.

Continue reading...