Formating "text" output to import into FMPRO

[drschilling]

Hi,

 

I'm getting some output from a vendor called Fillz and it's coming to me in text form as follows.  I'm running a pricing script on their server.

 

(Note: I have put a return between record sets though there isn't one in the real output.  There are 7 "records".  The first number is the SKU (Stock Keeping Unit)

 

I want to see the info as follows (in FMPRO) I just don't know how to separate it out.

 

SKU:

Base Price:

Condition:

Floor:

Sales Rank:

Total Offers:

Lowest Six Offers ( If I can separate out each of the lowest six offers into separate fields ie: (Lowest Six Offers P1, Lowest Six Offers P2, etc.)

Price:

New Price:

Change at Amazon:

 

 

072310-5-76
*** Base: 4.99, condition: 3
Floor: 3.99, sales rank: 28038, total offers: 1715
Lowest 6 offers: [[{P=2.28;S=3.99;C=11},{P=2.29;S=3.99;C=11},{P=2.5;S=3.99;C=11},{P=3;S=3.99;C=11},{P=3;S=3.99;C=11},{P=3.64;S=0;C=3}] OfferList]
price: 2.79
New price is: 2.79
price 2.79 is smaller than floor 3.99. Applying floor.
   change @ Amazon $4.99(base $4.99) -> $3.99

aw-1-100812-1064
*** Base: 6.99, condition: 2
Floor: 3.99, sales rank: 1124921, total offers: 627
Lowest 6 offers: [[{P=2.99;S=3.99;C=6},{P=2.99;S=3.99;C=5},{P=3;S=3.99;C=5},{P=3.18;S=3.99;C=11},{P=3.2;S=3.99;C=11},{P=3.21;S=3.99;C=11}] OfferList]
price: 3.10
New price is: 3.10
price 3.10 is smaller than floor 3.99. Applying floor.
   change @ Amazon $6.99(base $6.99) -> $3.99

0509101-5
*** Base: 4.99, condition: 2
Floor: 3.99, sales rank: 3844790, total offers: 157
Lowest 6 offers: [[{P=0.01;S=3.99;C=2},{P=3.59;S=3.99;C=11},{P=3.99;S=0;C=1},{P=3.99;S=0;C=2},{P=5.99;S=3.99;C=11},{P=6;S=3.99;C=11}] OfferList]
price: 3.93
New price is: 3.93
price 3.93 is smaller than floor 3.99. Applying floor.
   change @ Amazon $4.99(base $4.99) -> $3.99

0618101-3-56
*** Base: 4.99, condition: 2
Floor: 4.25, sales rank: 1161427, total offers: 181
Lowest 6 offers: [[{P=0.01;S=3.99;C=2},{P=0.01;S=3.99;C=2},{P=2.75;S=3.99;C=6},{P=2.75;S=3.99;C=6},{P=2.89;S=3.99;C=11},{P=2.9;S=3.99;C=11}] OfferList]
price: 1.89
New price is: 1.89
price 1.89 is smaller than floor 4.25. Applying floor.
   change @ Amazon $4.99(base $4.99) -> $4.25

0625101-1-7
*** Base: 4.99, condition: 2
Floor: 3.99, sales rank: 1069137, total offers: 689
Lowest 6 offers: [[{P=0.65;S=3.99;C=11},{P=0.7;S=3.99;C=11},{P=0.88;S=3.99;C=11},{P=0.91;S=3.99;C=11},{P=0.95;S=3.99;C=11},{P=0.99;S=3.99;C=11}] OfferList]
price: 0.85
New price is: 0.85
price 0.85 is smaller than floor 3.99. Applying floor.
   change @ Amazon $4.99(base $4.99) -> $3.99

f011411-j3-6
*** Base: 4.75, condition: 2
Floor: 3.99, sales rank: 23059, total offers: 1310
Lowest 6 offers: [[{P=0.01;S=3.99;C=2},{P=3.75;S=3.99;C=11},{P=3.75;S=3.99;C=11},{P=3.79;S=3.99;C=11},{P=4;S=0;C=2},{P=4.35;S=3.99;C=11}] OfferList]
price: 3.28
New price is: 3.28
price 3.28 is smaller than floor 3.99. Applying floor.
   change @ Amazon $4.75(base $4.75) -> $3.99

f1-091112-x1-655
*** Base: 4.75, condition: 2
Floor: 3.99, sales rank: 23059, total offers: 1310
Lowest 6 offers: [[{P=0.01;S=3.99;C=2},{P=3.75;S=3.99;C=11},{P=3.75;S=3.99;C=11},{P=3.79;S=3.99;C=11},{P=4;S=0;C=2},{P=4.35;S=3.99;C=11}] OfferList]
price: 3.28
New price is: 3.28
price 3.28 is smaller than floor 3.99. Applying floor.
   change @ Amazon $4.75(base $4.75) -> $3.99

 

Thanks kindly,

 

David

[comment]

IIUC, each item occupies 8 lines - so that if you import the file as is, you will end up with 8 records per item. If so, you could import the file into a temp table,  parse out each item's attributes in turn into variables, create a new record in the "real" Items table and populate it with the contents of the variables, then process the next 8 records, and so on.

 

To parse out a specific attribute, you could use something like:

 

Let ( [
prefix = "*** Base: " ;
suffix = "," ;
start = Position ( text ; prefix ; 1 ; 1 ) + Length ( prefix ) ;
end = Position ( text ; suffix ; start ; 1 )
] ;
Middle ( text ; start ; end - start )
)

 

This will extract the Base Price value from the second record in the series.

 

 

---

Of course, it would be much easier if Messrs. Fillz could just provide you with data in one of the standard formats that Filemaker can consume directly, without requiring all this rigmarole.
 

Edited December 1, 2012 by comment

[drschilling]

Thanks for our quick response.  Will try this!