How to fix data shifting?

Question

Hi there,

I have a .txt file which has a lot of data shifts. The lines are breaking to next rows. I have created a dummy data that is similar to my data set. Is there any way we could spot and concatenate row 4,6 and 8 to 3,5 and 7 respectively?

IDTITLE1045|XYZ|23.00|NULL|XYZ|AA2075|ABC|23.00|NULL|ABC|AA3049|XYZ|23.004|NULL|XYZ|AA5077|ERT|23.006|NULL|ERT|BB7060|BBB|23.00|NULL8|BBB|CC

The output should look like this:

IDTITLE1045|XYZ|23.00|NULL|XYZ|AA2075|ABC|23.00|NULL|ABC|AA3049|XYZ|23.00|NULL|XYZ|AA4077|ERT|23.00|NULL|ERT|BB5060|BBB|23.00|NULL|BBB|CC

Thanks,

Aria

vizAlter · Answer

Hi @Arunima_S - Try this solution:

How to fix data shifting__vA.yxmd

AngelosPachis · Answer

Oh there was already a post out there, didn't know that.

Guess the only difference is the modulo function, but that's a tiny detail.

AngelosPachis · Answer

Hi @Arunima_S ,

I used a formula tool to Regex count the number of | in each record.

Then, with a multi-row formula tool, I have created a running total of the | count, and since you should have 5 | in each record (because that's how many fields you should have) I created groups using the mod function.

So then I had the field that I can use to group on, and with a summarize tool I could then proceed to concatenate the different fields.

Let me know if that worked for you or you have any questions on the workflow.

Regards,

Angelos

example.yxmd