Regex - Extract data

[swap]

Hi,

I'm looking for a regex expression pattern for the following data. I need first column and last column information from the table. 

e.g. Annual Leave 12.5 Uncertified Sick Leave 6.0 48 mths Sick Leave Full Pay 148.5 48 mths Sick Leave Half Pay 195.0

Is it possible? Thanks.

                                Quota as     Taken as                         
Type                            of today     of today       Booked    Remaining
--------------------------- ------------ ------------ ------------ ------------
Annual Leave                   17.5 days     5.0 days     0.0 days    12.5 days
Uncertified Sick Leave          7.0 days     1.0 days                  6.0 days
48 mths Sick Leave Full Pay   195.5 days    47.0 days                148.5 days
48 mths Sick Leave Half Pay   195.0 days     0.0 days                195.0 days

[graham]

Yes, it certainly is possible

The solution all depends on how your data is structured

If the columns of data are separated by spaces, then a simple substring would enable you to pull out the data at the appropriate column divisions

If there are tab separators, then you would be able to split the row on the tab into a collection and pull out the relevent indexes.

If you really must use a regex, then you would need the start and end string anchors, but as your 'Type' field has varying numbers of spaces, that would be much trickier.

[swap]

Thanks graham for your reply. I got this unstructured data from other system through webservice. But this data is separated by uneven spaces. Hence it is difficult to capture it. As you said earlier, how to use substring on column division?

[graham · Accepted solution]

It looks to me as if there is a structure - of sorts

The hyphens underlining the column headers look as if they delimit the field sizes, therefore Id split the data into rows by using a collection operation to split on a line end

Then delete the first 3 entries to remove the headers (note, when you delete the first entry, the 2nd entry now becomes the new first one)

Then loop through the lines using a for each and use build strings to get substring of the variable, 1,27 for the Type and substring of the variable, 69,12 for the Remaining (I may have miscounted - trying it out will tell you if it is correct)

[swap]

Thanks Graham for your help. I've implemented it slightly different way but used same method what you have specified above. Cheers. Swapnil