Import File, store records to match exact fields

[stash]

Hi,

Have imported in database of FileMakerDeveloper7 a file with the records which look like:

12.32.22.199 - - [26/Aug/2004:05:08:00 -0400] "GET /amason.txt HTTP/1.0 404 346" "Googlebot/2.1 (+http://www.google.com/bot.html)"

99.15.255.284 - - [28/Aug/2004:06:04:50 -0400] "GET /Folder/picutre.gif HTTP/1.1 200 47418" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1) Opera 7.50 [en]"

1.0 All records are stored in one field(Imported_Array), because that's the only way how to manage fild mapping while Import.

2.0 Now and somehow, I have to brake each record and store to the fields in database to look like:

First_Separator = 12.32.22.199

Second_Separator = 26/Aug/2004:05:08:00 -0400

Third_Separator = GET /amason.txt HTTP/1.0 404 346

Fourth_Separator = Googlebot/2.1 (+http://www.google.com/bot.html)

First_Separator = 99.15.255.284

Second_Separator = 28/Aug/2004:06:04:50 -0400

Third_Separator = GET /Folder/picutre.gif HTTP/1.1 200 47418

Fourth_Separator = Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1) Opera 7.50 [en]

I did find way in one case:

Go to Record/Request/Page[first]

Loop

SetField[Table::First_Separator;Left(Table::Imported_Array;Position(Table::Imported_Array;" - -";1;1))]

Go to Record/Requested/Page/[Next; Exit after last]

End Loop

3.0 That store all IP addresses from Imported_Array to the field First_Separator.

4.0 At the moment, banging my had against the monitor and asking for help...how to Separate the rest of elements in Imported_Array and get them to the Separate fields 2,3 and 4?

Go to Record/Request/Page[first]

Loop

SetField[Table::First_Separator;Left(Table::Imported_Array;Position(Table::Imported_Array;" - -";1;1))]

SetField[Table::Second_Separator; to do something]

SetField[Table::Third_Separator; to do something]

SetField[Table::Fourth_Separator; to do something]

Go to Record/Requested/Page/[Next; Exit after last]

End Loop

Any help is welcomed and thanks to everyone who even try to deal with this problem!!!

[Wim Decorte]

You're on the right track.

To get the second string: it is anything between the first [ and the second ]

The third string: anything between the first " and the second "

The 4th string is anything that is left

(you might want to subsitute each string you extracted to a blank to make this easier).

[stash]

Hey Enthusiast,

Thanks for Great Encouragement!!!

I just got the second field by using this:

SetField[Table::Second_Separator; Let([start = Position( Table::Imported_Array; "["; "- -"; 1 ); End = Position( Table::Imported_Array; "]"; ""; 1 )]; Middle( Table::Imported_Array; Start+1; End-Start-1))]

[harvest]

Hi Stash,

why not use a texteditor before importing into filemaker?

three actions of "find and replace" will leave you with a file, you can simply import into fm, just choose ";" or Tab as new separators

Holger

[Lee Smith]

I agree with Holger.

Although you are well on your way of solving this parse problem, I find it easier to manipulate text in a text editor before importing it into FileMaker. Any text editor with the ability to use Grep pattern (Regular Expressions) Find and Replaces can make what you are wanting to do very simple. I use BBEdit or TextWrangler on the Mac (I think their equivalent on windows is TextPad).

This find and replace in BBEdit 5.x, using Grep Patterns, does what you need.

Find

^(d.*) - - [(d.*)] "(GET.*)" "([A-Z].*)"r

Replace

1t2t3t4r

HTH

Lee

[stash]

Thanks for advices about text editor, they're excellent...but, I like to explore the idea about module or program which does that instead of us. I'm just lazy and want mashine to mend it

Cheers!