Extracting data from semi-structured text files

faran_ahmed

Hi team,

I wanted to ask if there is a possibility with Regex or any other tool, to have a semi-structured data from the attached text file converted into what we have in the attached excel file?

I have maximum reports available in such structured text files so it will greatly reduce the effort in the workflow.

Cheers!

Test report.txt

Required output.xlsx

mceleavey

Hi @faran_ahmed ,

Yes, that's right. You would use a combination of Text to Columns and Regex.

That would be an entire project though, so to get you started you might want to look at the following community resources:

Regex:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-RegEx/ta-p/37689

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/RegEx-Examples-12-Handy-Use-Cases/td-p/36647

https://help.alteryx.com/20213/designer/regex-tool

https://community.alteryx.com/t5/Interactive-Lessons/Using-RegEx-in-Expressions/ta-p/424427

Text to Columns:

https://help.alteryx.com/20213/designer/text-columns-tool

https://help.alteryx.com/2019.1/TextToColumns.htm

https://community.alteryx.com/t5/Interactive-Lessons/Separating-Data-into-Columns-and-Rows/ta-p/80183#done

Hope this helps,

M.