Field Conversion from txt to CSV - 2019 version

[Jaganmohan]

Hi Team - I am facing an issue while converting txt to csv, could you please guide me on this, thanks.

Please find attached Input and required Output files for your reference.

[markcurry]

Hi @Jaganmohan 

See the attach workflow, hopefully that should get you most of the way there.

Your input file is a fixed width text file, so it might be good to look at in Notepad with a font set to Courier, as it has equal spacing per character.

The workflow identifies the report body, by selecting the lines between the ------ and Page :   Then it uses RegEx to split your text file into the first 6 characters, then the next 11, the next 6 and so on.  (You might need to double check the widths I have here for each field).  You could also use a series of Substring functions.  

Hope that helps.

[Jaganmohan]

Hi Markcurry,

Thanks a lot - I have a question on this,

what if my input file is not in a standard nature - in the given input (example) Column like issue characters(52) not the same all the time, it may varies.

could you please help me out by giving any alternative solution if such case happens in future ? Thanks in advance.

[markcurry]

Hi @Jaganmohan 

Usually where you get a report like this, they were designed to be printed to typically don't change.  

I mightn't have gotten the exact number of characters correct, as I just a had a quick look at creating a solution, so you might need to tweak this as you get more input data.  In the case of the Issue column, if this isn't always 52 characters would could look at other ways of doing it, like taking all the characters before USD, BRL, CAD, et c  .   

But this type of file is usually consistent.  And looking at your data again, I had Field Change down for 24 characters, Old Value for 20 characters and New Value for 22.  I think this should be 22 characters for each of these fields.

Sometimes you can be luckier with text files, where you have 2 or more spaces between the fields, and you can use a RegEx function to replace 2+ spaces with a |, then just do a Text to Columns splitting on the |.   This won't work for you unfortunately as you have empty fields, so it wouldn't align them correctly.

[Jaganmohan]

Thanks for the clarification - I am happy with the solution.

I think this should be 22 characters for each of these fields - you are right.

Tool perfectly working, if i restrict the character length to 22  for said fields, thanks a lot once again.