Importing fixed width text files

[GlennC]

I need to import text files in fixed width format into FM. I can first import into Excel and from there to FM - but - excel wants to treat each row as a seperate record; I need several of the rows (lines in the original text file) to form one record, so I don't know how to make that work. I need to do this regularly - several files each day. I'm a babe in the woods with importing. Any and all help greatly appreciated.

Glenn

[Lee Smith]

Hi Glenn,

Excel and FileMaker both handle returns and tabs in the same way (Tab Delimited file). That is, each Tabs equal next field (column) and each return equals next record (row). As you have found out, both applications are really hard to manipulate text in.

You would better off making your changes in a text editor before you bring it into FileMaker. I use BBEdit for this purpose because, it has several tools that are made for cleaning up text, that you won't find in a standard Word processor. Although BBEdit is a Mac application, there is a Win equivalent. You can download BBEdit lite (Free version) at

http://download.com.com/3120-20-0.html?qt=BBEdit&tg=dl-2003&search=+Go%21+

HTH

Lee

[cjaeger]

Import your data as tab delimited file into an extra field ("import")

then create fields with an auto-enter calc like this:

field1=Trim(Middle(import,1,10)) 10 if first field is 10 chars long...

field2=Trim(Midddle(import,11,30)

...

last field= Trim(Middle(import,400,99999)) - Filemaker fields can hold only 64000 characters, so 99999 gets all the rest.

a similar technique can be employed if you have text with a different delimiter as <tab>. But here a trip to a text processor doing a quick search/replace is faster ("|" -> <tab>)...

[cjaeger]

reading your post again, i saw you need to import several rows into 1 record.

see my attachment in this thread

http://www.fmforums.com/threads/showflat.php?Cat=&Board=UBB15&Number=63943

Combine this with the technique skeched above.

Looks as if you are importing vcard or edifact data ....

well, then you will have to do some additional parsing. The structure is not really fixed width, but contains some field identifiers plus variable length text plus fixed with plus several lines.

[GlennC]

Thanks Lee. I can't find any trace of BBedit for PC - not at the link you gave or from a general search. Are you sure? Having read a little about it it sounds like it would be handy.

Glenn

[GlennC]

Thanks very much Christian. Through necessity I had arrived at a similar outcome,though alot less elegant. I have learnt from the attachments on the link you gave, although some of it is over my head. Thanks.

[Lee Smith]

Hi Glenn,

Here is the deal. I do not have a way to test these, so if you find any of them helpful, please let me know.

http://www.textpad.com/

[color:"green"](Reluctantly recommended by one of the developers at BBEdit, I guessing this one comes the closest.)

http://www.vim.org

[color:"green"](Recommended by one of the list members of their talk list)

EditPad Pro.

http://www.editpadpro.com/

PowerGrep.

http://www.powergrep.com/

[color:"green"] These two were recommended by another list member

Lee