Columnar data to table conversion with missing fields

[bfarlow]

Hi all, newbie here

I'm trying to take previously extracted data for multiple records which have 56 fields currently represented in two columns (field name and data). I have found a premade filter called "convert column delimited to record.fll" which looks like it will work well with some modification, however, I cannot find a method for the search and replace to recognize when a field is missing (and not just the data but the field name too) from a specific record and to place a space in the appropriate place to keep the data aligned with the headings properly.

I am attempting this in TextPipe in an effort to eliminate Excel from the process (I have a process in Excel that does this well currently). Can anyone give me any guidance?

[DataMystic Support]

Hi there,

As we described in an email, use a Restriction to match the set of fields, then inside it sort the lines so they are in a consistent order.

Then, use an EasyPattern pattern match that allows for optional sections e.g.

[ longest optional( 'field1=', capture( 0+ not cr or lf ), cr, lf,
longest optional( 'field2=', capture( 0+ not cr or lf ), cr, lf,
..
longest optional( 'field56=', capture( longest 0+ not cr or lf )
]

Replace with

"$1","$2",....,"$56"

[bfarlow]

If I sort the fields won't that lose their relationship with their record? My data looks like this:

Status: A
MLS: 1234
AD: 123 Able St
Town: Mytown
Status: A
MLS: 5678
AD: 456 Baker St
Town:
Status: A
MLS: 9012
Town: Yourtown

The columns are seperated by ":", the first column is the field name, the second is the data. In some cases the field is given but the data is missing, in other cases the field and data is missing. I need to account for the missing fields when setting up the table so that data is ordered appropriately.

[DataMystic Support]

You need to Sort within a restricition.

Say that Status and Town are always present. Use a perl pattern search/replace filter as a restriction,

Status.*Town([^\r\n]*?)

Replace with $0.

*inside* this, place a sort filter.

If your fields are always in the same order, you don't need to do this step.