Importing column format from text file

[cabsandy]

Hi,

I've been away for a while so excuse me-I've attached an example of what I'm trying to import if this makes things easier.

From the text, which comes from a router we use, I'd like to import the information into FMP ver 6 in the "normal" format-this is, 4 field columns named Sid, Good, Corrected & Uncorrected. From that, I can set up my relationships and export functions which I've got a handle on, due in no small part to these forums. Unfortunately, when I try to import now, I just end up with one long field column which I cant use

Can it be done?

Thanks

Sandy

cmfecexample.txt

[QuinTech]

Hi, Sandy. The way the text is formatted, FMP will always recognize it as one field and x records, where x is the number of lines. One way you could get it as 4 fields and (x/4) records, which it seems you want:

In a text editor, replace every [CR/LF] (carriage return/line feed) with a comma. Then replace

,S

 with 

[CR/LF]S

. Since the string 

,S

will be unique in your import file and will always denote the beginning of a new record, this should give you the result you want.

Once the data is imported, you'll have those convenient colons in your FMP database, so you can isolate the data you want with the script steps:

Replace Contents [sIDField,Middle(SIDField,Position(SIDField,":",1,1)+2,64000)]

likewise for the other fields...

HTH,

Jerry

[Lee Smith]

Hi Sandy,

My recommendation would be to fix the file in a text editor before importing it. Although you can do this with FileMaker, IMO it just isn't worth the time and effort to set it up.

Do you have TextPad, Crimson Editor or HomeSite. I understand that these programs are like BBEdit or TextWrangler for the Mac, and can do Grep Pattern Find and Replaces, (Regular Expressions, etc.), If so, you can manipulate your file using 3 or 4 find and Replaces for easy import into FileMaker. If you need some help with the patterns, let me know.

Lee

[cabsandy]

Lee,Jerry,

Thanks for the reply-I didn't think this would be easy.I've done some work on it whilst waiting to see if somebody knew a quick answer.Could you take a look at my feeble attempt and let me know if there is no way that the info from the number count field could be added to its respective field? The information from the router will always be in this format.What I cant work out if there is a scrip step or loop that will allow me to say "in the sid field, take the first record from the number count field and insert.Then move to record 5 of the number count field and insert into sid field" and so on.I would need to follow this process for each field.

Sandy

fec.zip

[Lee Smith]

Hi Sandy,

Importing your list as is, as you see brings each line in as a separate records. I don't think this will be very helpful to you, as there isn't any way to associate the individual records. You need to separate your fields by Tabs or Commas, and each record needs to be separated by a return.

This is why I asked what text editor you have.

Lee

[Lee Smith]

1 93400 3 0

2 19465751 1397 157

3 98298 0 0

4 94650 0 2

5 487324 5 0

6 93870 0 0

7 95289 3 1

8 300874 5 1

9 739371 16 1

10 2076025 42 6

Copy this into a text editior and import it, and see if it isn't what you are looking for. (there are tabs between the fields

[cabsandy]

Lee,

It's what I'm looking for all right-confirm for me that the following ar the right steps

1. Save as text file

2. Import into Excel and save as .csv file

3. Import .csv file into FP and use as desired

I'm not sure if I'm doing too much in that I should be able to import tab files directly? How did you get the raw text?

Sandy

[Lee Smith]

Hi Sandy,

Try this. If you don't have one of the Text Editors I mention before, you need to find it and download it.

Next, Save the file as text and use one of Text Editors to do and it's ability to use Grep Patterns to do the following finds and replaces.

Remove double returns that show at the top of your file if they are really there. Do this manually or by Find and Replace like this:

Find

rr

Replace

r

Next, replace all the spaces before and after the colon, by doing a Grep Find and Replace. BTW, just select these as typed and put them in your Find and Replace boxes. You can save patterns in BBEdit my adding them in the Find Menu and naming them.

Find:

([ ]+: )

Replace:

t

Next you want to get rid of the Field Names. I have an older version of BBEdit, so I can't streamline them to the new products. However these should work as I have pasted them. and beware that there isn't any loose returns or spaces added to your copy and paste.

Find:

([unc.*](t[d]+)r

Replace:

rSid

[cabsandy]

Lee,

Many thanks for the reply-I appreciate the help.But this would have been a process for many files, over a cosiderable period of time.The manual manipulation will take up too much time-I was hoping all this work could have been achieved internally within FP.So I'll reckon I'll have to pass on using Filemaker and find another solution-if one exists.

On another level, I find it surprising that FP does not have the flexibility to get round this sort of problem as searching through the forums would indicate that there are a few others who have files to manipulate, that are preseted in this format.

Thanks again

Sandy

[RodinBangkok]

Sandy, try the attached example. It gets you part way there, did not have time to write the script to transfer the global to fields, but this is not difficult, just loop a new record script transferring the calc data to num/txt fields and index the iteration field, exit the loop when the quanrecs=iteration. If you need help with advise.

Rod

AnotherParse.fp5.zip

[RodinBangkok]

Ok, had a second wind, heres the file again with script to add records, this should work, just perform the script imortrecs, and you should get 10 records. Note I did not test yet on PC, I think the cr/lf should be filtered properly, but if not you may need to tweek the calc fields.

Rod

AnotherParse2.fp5.zip

[cabsandy]

Rodin,

Ahh-this looks good! You know I'm going to ask for tweeks.My complete amount of records has 1507-that 1507 x 4 lines. What is the limitation you mention in terms of text field size limit?

Can the file be ammended to include an import records, rather than a cut and paste? I've managed to get the export working-this is the important part for me as once I have the records out, then I can apply my other scripts-which are looking good at the moment.Let me know what you think.

Thanks

Sandy

[RodinBangkok]

Sandy, I think the limit is 256K in FM6, you can try and import or drag and drop your full file into the txthldr field, if it rejects the drop or import, its too large. I'd try and drop or paste the file size you want into the txthldr field, if it does not accept, well too big, you'll need to append its size till it accepts, FM7 allows much larger text fields, however at this point (V3), the speed is so slow its not worth considering as importing a large text field on a 1Ghz machine can take up to 20 minutes to commit the record.

Rod

[cabsandy]

Rod,

I've tested this out and it looks to have done the job-thanks.One further question-I've noticed that if you continually paste into the text header field and then rerun the script, you can get all your records in-just takes a bit of time.I dont suppose there is a way to automate the loop script to "ask" for new sets of records? I see this as a way to overcome the FP ver 6 text limit size?

Sandy

[RodinBangkok]

Once the fill script completes you can clear the holder global and prompt the user via messages or another layout. If possible, much cleaner to test the import file first for size and put it in before it gets too large.

Rod