SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Issues loading CSV

    I'm trying to help the organization I'm in look at other options than their stuffy methods that have been being used.

    The firewall group has these log files. I've been asked to generate a PHP script that will parse the relavent data out and put it in an Access database.

    Forgetting the Access part for now, I need to get the following info.

    Outbound IP's
    Port
    Type of Traffic (TCP,UDP)

    A sample bit of a log gnerically looks like this:
    Code:
     2004-05-02 14:00:02	Local4.Info	xxx.xxx.xxx.xxx   May 02 2004 14:04:18: %PIX-6-302013: Built outbound TCP connection 359406235 for outside:xxx.xxx.xxx.xxx/25 (xxx.xxx.xxx.xxx/25) to inside:xxx.xxx.xxx.xxx/4219 (xxx.xxx.xxx.xxx/4219)
     2004-05-02 14:00:02	Local4.Info    xxx.xxx.xxx.xxx    May 02 2004 14:04:18: %PIX-6-302013: Built outbound TCP connection 359406236 for outside:xxx.xxx.xxx.xxx/25 (214.1.3.16/25) to inside:xxx.xxx.xxx.xxx/3896 (xxx.xxx.xxx.xxx/3896)
    I wanted to do this all in PHP, but the logfiles are upwards of 70MB a piece and I haven't found a good way to do it. Any suggestions along these lines would be helpful.

    So I've been trying MySQL LOAD_INFILE for a bit. It's almost working.

    There are essentially four tab delimited columns here.
    Column 1 - Date
    Column 2 - Type of Warning
    Column 3 - Source IP
    Column 4 - Warning

    I have used this query:
    Code:
     LOAD DATA INFILE 'c:\\log.txt'
     INTO TABLE logs
     FIELDS TERMINATED BY '\t'
     ESCAPED BY '\\'
     LINES TERMINATED BY '\n';
    It grabs the first three columns fine but the last one it doesn't read.

    All four fields in MySQL are varchar not null no default.

    It does seem to notice 4 fields as the 4th mysql field is consistently empty.

    Any ideas how to remedy this? That fourth field has the most critical data...

    Aaron
    Aaron Brazell
    Technosailor



  2. #2
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Anyone...
    Aaron Brazell
    Technosailor




Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •