Trim out each word into separate field

[Leb i Sol]

Hello everyone,

It has been more than while so be gentle with my ignorance and jump from 6-8.5.

Perhaps the post belongs to Import/Export section but it sort of touches both.

I am working a small solution that is to import log files...essentially a single line or 'columns' separated by a space. "Space delimited" if you like.

1. Attempted ODBC

I have tried ODBC import with pre-mapped fields but noticed that IPs can't be formated correctely.

eg. text field: 192.168.1.115 is actually imported in format of: 192.1681 so it seems like an issue of having '3 decimal points'...

Q:but it is text field, why would it care how many 'dots' are being imported?

2. Import File and Trim

Next option is to use import file.

What I end up with a table with a single field containing a string/log record eg.

6/25/2007 9:43:13 AM httpscan[4691] 1182789793 1 192.168.1.115 209.85.147.19 text/html 192.168.1.115 http://mail.google.com/mail/channel/bind? 307 3767734cc16059e52447ee498d31f822 ALLOWED CLEAN  2 0 0 0 0 - 0 - 0 - 0 mail.google.com web-based-email ldap0:userx

Q:

I was wondering how to go about developing a logic of:

c_Field1 = 1st 'word'

c_Field2 = 2nd 'word'

Is this even wise approach considering I will be dealing with 100,000s of records imported per week or should I just battle out the ODBC import/format issue?

Thanks for your time!

[Lee Smith]

Hi Alen, Welcome back.

How about an example of what is the first word, second word, etc.

Lee

[Leb i Sol]

Hi Lee,

Nice to read some 'old' faces here

One record is above in the code.

It is a log/text file so it just loops x times per row.

There is always 29 spaces in a single row which give me 30 'words' or better said columns.

Down below would be considered 7 columns:

6/25/2007 9:43:13 AM httpscan[4691]: 1182789793 1 192.168.1.115 ..etc.

Is this too much for Trim/count to handle?

Thanks a bunch!

[Lee Smith]

I think part of your problem is that there is a character separates the words that isn't a space, but when I look at it TextWrangler, it is a grey bullet.

The first thing I would do is to replace the bullet (space) with a Tab, or Pipe character, and then use the LeftWords, MiddleWords, etc. to parse it.

Better yet, If you can run though a text editor (i.e. BBEdit, TextWrangler, (words version I can't remember of the top of my head), strip the character there, and replace with a Tab.

Let me know if you need help with the Greps.

Lee

[Leb i Sol]

hmm no bullets here (notepad or editpad lite) just spaces. So 'find space' & 'repleace with tab'?

I was hoping to have FM handle this as the file(s) will get rather large. 210,000 records in about 120MB text file for 7 days worth of a log.

Find & repleace in text editor would be a pain.

I am worried about ODBC being able to handle all this...so I guess I have to put faith into script.

Thanks again.

[comment]

I think you should try hard to get a source that can be imported directly, such as a tab-delimited file. If not, you can import into a single field and parse it out into individual fields like this:

Let ( [

listOfValues = Substitute ( ImportedField ; " " ; ¶ ) ;

valueNumber = 3

] ; 

GetValue ( listOfValues ; valueNumber )

)

This example gets the 3rd value ("AM" in your example). For each field, set the appropriate valueNumber.

[Leb i Sol]

Hi comment,

Thanks for the script!

Trust me I have tried to get tab delimited from the support but no love. The best I can get is this space delimited log pulled from the syslog server. Which explains why manufacturer doesn't provide any 'reporting' tool but rather just this log output. I even suggested them to use ODBC based logging so that end users can choose the DB of choice and run their own reports.

This is why I pulled out the FM cd .

Thanks again!

[comment]

It's not a script, it's a calculation. Or rather a recipe for 30 calculations. You could have 30 auto-entered fields filled during import, or - if you only want to report on the data, but not edit it - 30 calculation fields looking at a common imported field.

Make sure you match data type to the intended result of each field.

[Leb i Sol]

Right, the stored calc fields is what I meant...rather then remembering to use a calculation every time I will just store them. Works great, thanks again.