Importing macintosh simpletext files into FMPro5?

[justifi]

I have been pouring over books and the help section of filemake pro but I haven't been able to figure out how to best import a simpletext document into filemaker pro and save time. Simpletext doesn't have any documenting structure. I know it'a text file and I have tried a few different options. 1)I imported the simple text document into word. Then I replaced all ( : and (=) in the document with (,) to give it some kind of structure to be imported into filemaker fields. Then I imported them into excel so that they would have defined rows and columns. This whole procedure is very time consuming! When I finally import the records into filemaker, I can only import one to two pieces of data by using the custom import button and matching the fields to the imported data.

There has got to be a better way! From where it stands right now it would be faster to type in all the data manually which would take quite sometime. But it appears more efficient then the way I have been going about it. Unfortunately all the important infomation that I want to place in a data base was downloaded into simple text. I welcome any suggestions from anyone that has worked through this problem. Thank you.

[ April 25, 2001: Message edited by: LiveOak ]

[BobWeaver]

Can you describe how the data in the simple text document is arranged, or better yet, show us a sample (ie, paste some in a reply to this topic).

[justifi]

This is a sample of part of the document. Simpletext is a stationary pad without a defined form.

ProductSKU1=CST

ProductDesc1=Cashmere Socks Taupe

ProductVariation1=Sizes=Medium/Large, submit=Add to Cart

ProductQty1=1

ProductPrice1=USD 40.00

ProductShipping1=USD 0.00

ProductTotal1=USD 40.00

ProductSKU2=CSMB

ProductDesc2=Cashmere Socks Midnight Blue

ProductVariation2=Sizes=Small/Medium, submit=Add to Cart

ProductQty2=1

ProductPrice2=USD 40.00

ProductShipping2=USD 0.00

ProductTotal2=USD 40.00

Base Shipping: USD 0.00

Total Shipping: USD 0.00

Sales Tax: USD 0.00

Order Total: USD 80.00

Thank you.

[BobWeaver]

Hmmm, this is not a nice format. It most closely resembles sylk format by the way the data is arranged. Having said that, I don't think there is really any practical way to convert it into one, because it would take a dozen or so text replace commands to get it into the right format. Can you get the data in a different file format such as tab or comma delimited etc.?

It can be imported, as is, one line per record into a Filemaker file to hold the raw import data and then you would run a reasonably complex script to go through the data and parse it into real records and add them to another Filemaker file. Do you feel up to it? If so, I can give you some basic guidelines.

[justifi]

Sure, I'm always willing to learn something new! I don't know if I'm up to it but I can give it a shot.

Thank you

[Gerd Muller]

Have a look at "Torquemada". This is a powerful search and replace software, where you could give your simple text document a structure.

Gerd

[BobWeaver]

If you decide to do it all in Filemaker, then here is what I suggest.

Import the text into a separate file called ImportDB, so that all of one line goes into a single field called RawText. This will give you as many records as you have text lines in the text file.

Make a script that loops through each record and searches for the various keywords and sets a global field depending on which keyword is found. The steps in the script will look like this:

code:

---

Go to Record/Request [First]

Set Field [gItemNumber = 1]

Set Field [gOrderNumber = 1]

Loop

If [PatternCount(RawText,"ProductSKU")]

Set Field [gProductSKU, Middle(RawText,Position(RawText,"=",1,1)+1,64000)]

End If

If [PatternCount(RawText,"ProductDesc")]

Set Field [gProductDesc, Middle(RawText,Position(RawText,"=",1,1)+1,64000)]

End If

If [PatternCount(RawText,"ProductVariation")]

Set Field [gProductVariation, Middle(RawText,Position(RawText,"=",1,1)+1,64000)]

End If

# and so on for ProductQty, ProductPrice and ProductShipping

.

.

# check for a record break keyword (ie, the ProductTotal)

If [PatternCount(RawText,"ProductTotal")]

Set Field [gProductTotal, Middle(RawText,Position(RawText,"=",1,1)+1,64000)]

# Now, use these global fields to create a new record

# in a related LineItems file.

Perform script [External, LineItems:MakeNewRecord]

# Now, increment the Item Number

Set Field [gItemNumber = gItemNumber + 1]

End If

# check for Order break keyword

If [PatternCount(RawText,"OrderTotal")]

Set Field [gOrderTotal, Middle(RawText,Position(RawText,":",1,1)+1,64000)]

# Now, use these global fields to create a new record

# in a related OrderSummary file.

Perform script [External, OrderSummary:MakeNewRecord]

# Now, increment the Order Number

Set Field [gOrderNumber = gOrderNumber + 1]

End If

Go To Record/Request [next, exit after last]

End Loop

---

The External MakeNewRecord scripts should simply create a new record in the respective external file, and set the field values to the respective global values in ImportDB. The globals are accessed in the external files by a constant relationship back to ImportDB (a calculated field [=1] in each file) .

This will create records in two related files, a line items file which will have a record for each product sold, and an order summary file which will have a record for each order. These two files can be related by the order number fields. Hope this helps.

[justifi]

Holly Cow Bob!!! I haven't been back to check the forum since the last message you posted about checking out that software with the search and replace features. At the time I was overwhelmed with filemaker pro so the last thing I wanted to do is learn yet another program. A member of this forum, ammccallum, introduced to me what on the surface appears to be simpler solution then the script option you created. That is not to say that yours isn't better or worst but that I haven't advanced to scripting yet. I just posted an over all explanation of what he shared with me in another post in this topic area. I will probably come back to your idea, when I'm ready to script unless you have a way of explaining the same information that is not so daunting. I thank you for you time and any other comments that you might like to share with me.