Columnar data to table conversion with missing fields

Get help with installation and running here.

Moderators: DataMystic Support, Moderators

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

Columnar data to table conversion with missing fields

Postby bfarlow » Sat Dec 09, 2006 1:29 am

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: 2154
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Postby DataMystic Support » Mon Dec 11, 2006 9:54 am

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"
Regards,

Simon Carter, http://DataMystic.com/forums/index.php
http://PredictBGL.com - Insulin dose calculator for Type 1 diabetes
http://DownloadPipe.com - 250,000 free software downloads
http://DetachPipe.com - send huge email attachments

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

Postby bfarlow » Wed Dec 13, 2006 1:32 am

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: 2154
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Postby DataMystic Support » Wed Dec 13, 2006 7:51 am

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.
Regards,

Simon Carter, http://DataMystic.com/forums/index.php
http://PredictBGL.com - Insulin dose calculator for Type 1 diabetes
http://DownloadPipe.com - 250,000 free software downloads
http://DetachPipe.com - send huge email attachments


Return to “TextPipe Tips and Tricks, Questions and Support”

Who is online

Users browsing this forum: Bing [Bot] and 1 guest