Extracting vertical format data for a column irrespective of its location given in multiple columns - column location can be interchanged by other columns

Question

Hi,

Looking for a one liner RE for extracting vertical format data from a specific column. Things that are expected from the RE is :-

a) It should not catch the column based on its location as there is high probability that column can be at different location and a wrong data may be fetched from another column.

For example in the below given 'Data Sample' I wanted to extract data for the column "Area" which is the first column in the provided data sample:- 

Area Port Media Speed State Protocol     # Columns
===========================
0 0 id A2 Online E-Port
1 1 id C4 Online E-Port
12 12 id 4X No_Light
13 13 id 4Z No_Light Disabled
14 14 id 2G No_Light
15 15 id 4E No_Light Disabled
16 16 id U1 No_Sync
17 17 id 4V No_Light Disabled
18 18 id N2 No_Light Disabled

Now, in 'Data Sample 2' the location of column "area" is interchanged with column "port". In this case the RE I have written based on column location will fail to extract the right data and will catch the wrong data.

Considering the above scenario I am looking for a solution or an approach of an one liner RE that will help me extract :-

** right data for column "area" irrespective of its location specified in the set of columns.

** should catch nill data compared to catching wrong data.

Your suggestions in this regard is appreciated.

Regards,

---

Ankur Akash

Answer 1

Hi,

I don't know if this can be solved with one line regexp, since you don't know the position of the data what you are looking for.

In two steps it can be solved easily even without regexp:

1. Split the header line and search the index of "Area"
2. Iterate through data lines, split them and get the member for the previously defined position

Answer 2

Hi Ankur,

Welcome to the MSDN Forum.

Please try this RE:

(?<area>\d+\s)(?<port>\d+\s)

When you need area value, just get the area group, when you need port value, just get port group.

Best regards,

---

Mike Feng
MSDN Community Support | Feedback to us
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 3

Thank you for your responses.

Hi James,

I am not sure how I should go ahead with your proposed solution as I was possibly looking for a one liner RE to fix this issue.

Hi Mike,

I tried your suggested RE, it works, but it's position specific. Say in the above example if the first column "area" and the second column "Port" interchange their location the suggested RE will catch wrong data.  I have hard coded to get the solution by writing for two-three different patterns but I was looking for a generic grammar which is only applicable when I can catch the data of respective columns irrespective of column locations.

Regards,

---

Ankur Akash

Answer 4

Hi Ankur,

I am a little lost you.

Would you like to provide more data samples and results?

Thank you very much.

Best regards,

---

Mike Feng
MSDN Community Support | Feedback to us
Please remember to mark the replies as answers if they help and unmark them if they provide no help.