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!

Hi Alen, Welcome back.

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

Lee

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!

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

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.

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.

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!

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.

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.