Columnar data to table conversion with missing fields

Get help with installation and running here.

Moderators: DataMystic Support, Moderators, DataMystic Support, Moderators, DataMystic Support, Moderators

Post Reply
bfarlow
Posts: 2
Joined: Sat Dec 09, 2006 1:22 am

Columnar data to table conversion with missing fields

Post by 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?
User avatar
DataMystic Support
Site Admin
Posts: 2227
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Post by 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
Posts: 2
Joined: Sat Dec 09, 2006 1:22 am

Post by 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.
User avatar
DataMystic Support
Site Admin
Posts: 2227
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Post by 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.
Post Reply