Nedd your help for space seperated data with some different format

[alt_tush]

Hi Team,

I have a space separated data in text file.

Format of the data are below :-

ABC20180322 00000MAR 0000000000000000000000000000000000000000000000000000000000

1 ABC 10 HR

2 DEF 20 ADMIN

3 PQR 30 IT

4 XYZ 40 HELPDESK

ABC20180322 00000MAR 0000000000000000000000000000000000000000000000000000000000

I want all the 4 records in excel in between first and last row.

First and last row should be ignore. Also there would be "N" number of records in between first and last row.

Please need your assistance here.

Please help

Thank you in advance.

[LordNeilLord]

Hey @alt_tush

If you're data is always going to be in that consistent format, I'd use regex to identify a pattern...for example the first column always contains a recordid...then you can filter out the fields you don't need.

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

[alt_tush]

Hi Neil

Thanks for solution but my all fields are alphanumeric.

There is no any field is numeric value. In such case how can I handle this situation?

Once again thank you.

[LordNeilLord]

Hey @alt_tush

Does the header always have those 00000000000000000000000 in the header?

An actual example of the headers would be useful

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

[alt_tush]

Yes header would be always same. But data is always alphanumeric in all the fields.

Thank you.

[LordNeilLord]

But if it always has a consistent format or pattern you can search for that...for example if the header always has 10 number in it you can use RegEx_Match(Field1, '\d{10}') and it will find anything that matches that

[BenMoss · Accepted solution]

Would a simple solution not be to use the sample tool.

First of all recordID your data.

Use the sample tool to 'skip the 1st n(1)' record.

Then sort your data on the recordID descending.

Use the sample tool again to 'skip the 1st n(1) record.

Resort your data back into the original order (ascending).

Ben

[alt_tush · Accepted solution]

Yes Ben. Your are right :)

It works now.

Thank you so much for your help :)

[alt_tush]

Hi Neil,

Thank you for your help.

I got the solution as Ben shared with us. He suggest me

First of all recordID your data.

Use the sample tool to 'skip the 1st n(1)' record.

Then sort your data on the recordID descending.

Use the sample tool again to 'skip the 1st n(1) record.

Resort your data back into the original order (ascending).

Thank you so much you help me always :)