Challenge putting data in different columns from excel - Difficult format

Hi Team,

Hope you are well. I am working on a project where i have to extract information from the invoice. I have used python to did that. The excel file exported from python with all the information from invoice. unfortunately it's not in the desired format.

Please find attached the input file and the desired output file. Ideally i am looking for help where the workflow picks up the keyword from the excel file the corresponding value from the input file (for e.g Amount, total, invoice number, invoice date) from each row of the input file and create a new output file with all the values and columns.

Please let me know how to go about it, i need your help,

Thanks,

Reporting

Regex

Batch Macro

Dynamic Processing

Data Investigation

Iterative Macro

Accepted answers

Hi @sriniprad08,

You're right-- this data is in a very difficult format!

Here is one possible solution. However, it is highly dependent on regex, so it might have trouble with widely varied data. For example, I parsed invoice number as anything within the 16 closest characters to the word "invoice" that was not a dollar amount and does contain at least two digits with possible letters and dashes on either side.

Screen Shot 2021-01-25 at 11.38.09 PM.png

parse invoice information.yxzp

Hi @sriniprad08,

This article should help you with workflow from older Alteryx versions.

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Adjusting-Alteryx-Files-for-Different-Versions/ta-p/26237

Hi @sriniprad08,

I have updated @clmc9601 workflow.

I have changed this part in notepad:

as this was created with 2021.1 version.

Please check if you can open it now.

parse invoice information.yxmd

All comments

What exactly are you asking for here? I see a lot of input files, but no indication of how the output should look.

Thanks for the message. The output file is already attached there.

I am looking for key fields from "input file" into "output file".

Hi @sriniprad08,

You're right-- this data is in a very difficult format!

Here is one possible solution. However, it is highly dependent on regex, so it might have trouble with widely varied data. For example, I parsed invoice number as anything within the 16 closest characters to the word "invoice" that was not a dollar amount and does contain at least two digits with possible letters and dashes on either side.

Screen Shot 2021-01-25 at 11.38.09 PM.png

parse invoice information.yxzp

Great thank you so much. Can you please let me know which version of alteryx you are using. When i try to import its not allowing me .

Hi @sriniprad08,

This article should help you with workflow from older Alteryx versions.

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Adjusting-Alteryx-Files-for-Different-Versions/ta-p/26237

hi @clmc9601 Thank you . It works like a magic.

Really appreciate it. I will check if i can try with different formats.

Thanks

Sri

Thank you for all the help. One question how to arrive at a particular regex suitable for our field? is there any way?

Cheers,

Quick Links

Tags

This months top contributors

Hollingsworth 335