Challenge putting data in different columns from excel - Difficult format

[sriniprad08]

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,

[Lisa_M]

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

[sriniprad08]

Hi @Lisa_M ,

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

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

[clmc9601 · Accepted solution]

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. 

[sriniprad08]

Hi @clmc9601 ,

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 .

[Emil_Kos · Accepted solution]

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-Differe...

[Emil_Kos · Accepted solution]

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. 

[sriniprad08]

hi @Emil_Kos  Thank you so much . i could run it . 

Really appreciate your help here.

Thanks,

Sri

[sriniprad08]

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

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

Thanks

Sri

[sriniprad08]

Hi @clmc9601 ,

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

Cheers,