Load data from text file

Discussion in 'Databases' started by siddharth007, Nov 15, 2012.

  1. #1
    Hi all. I need to insert data from a text file(on my local machine) into a mysql database table.I know that it can be done using 'LOAD DATA INFILE' command in mysql.However i am finding it difficult to parse the rows in my text file for the columns.My text file looks like this


    fortunep pts/6 :0.0 Fri Nov 9 14:40 - 14:49 (00:09)
    fortunep pts/5 :0.0 Fri Nov 9 14:29 still logged in
    fortunep pts/4 :0.0 Fri Nov 9 13:02 still logged in
    fortunep pts/4 10.1.66.32 Fri Nov 9 12:13 - 12:23 (00:09)
    fortunep pts/1 :0.0 Fri Nov 9 11:11 still logged in
    reboot system boot 3.2.0-31-generic Fri Nov 9 11:03 - 15:23 (04:20)
    fortunep pts/3 :0.0 Thu Nov 8 18:44 - 19:46 (01:01)
    fortunep pts/1 :0.0 Thu Nov 8 17:36 - 19:47 (02:10)
    reboot system boot 3.2.0-31-generic Thu Nov 8 17:35 - 19:47 (02:11)
    fortunep pts/4 :0.0 Thu Nov 8 16:58 - 17:24 (00:25)
    fortunep pts/1 :0.0 Thu Nov 8 16:08 - 17:21 (01:13)
    fortunep pts/4 :0.0 Thu Nov 8 13:08 - 13:11 (00:02)
    fortunep pts/4 :0.0 Thu Nov 8 12:53 - 13:00 (00:06)
    fortunep pts/4 :0.0 Thu Nov 8 12:28 - 12:53 (00:25)
    fortunep pts/3 :0.0 Thu Nov 8 11:25 - 17:24 (05:58)
    fortunep pts/1 :0.0 Thu Nov 8 10:41 - 13:54 (03:13)
    reboot system boot 3.2.0-31-generic Thu Nov 8 10:36 - 17:24 (06:47)
    fortunep pts/3 :0.0 Wed Nov 7 11:05 - 19:23 (08:18)
    fortunep pts/1 :0.0 Wed Nov 7 11:04 - 19:23 (08:19)
    reboot system boot 3.2.0-31-generic Wed Nov 7 10:54 - 17:24 (1+06:29)
    fortunep pts/1 :0.0 Tue Nov 6 18:47 - 18:49 (00:01)
    reboot system boot 3.2.0-31-generic Tue Nov 6 18:24 - 18:58 (00:34)
    fortunep pts/1 :0.0 Tue Nov 6 15:01 - down (03:11)
    fortunep pts/1 :0.0 Tue Nov 6 14:39 - 15:00 (00:20)
    fortunep pts/4 :0.0 Tue Nov 6 14:30 - 18:12 (03:42)
    fortunep pts/4 :0.0 Tue Nov 6 12:42 - 14:30 (01:47)
    fortunep pts/1 :0.0 Tue Nov 6 12:25 - 14:39 (02:14)
    reboot system boot 3.2.0-31-generic Tue Nov 6 12:24 - 18:12 (05:48)
    fortunep pts/3 :0.0 Fri Nov 2 17:25 - 19:16 (01:51)
    fortunep pts/1 :0.0 Fri Nov 2 14:40 - 19:16 (04:36)
    reboot system boot 3.2.0-31-generic Fri Nov 2 14:39 - 19:16 (04:36)
    fortunep pts/3 :0.0 Fri Nov 2 13:44 - 13:48 (00:04)
    fortunep pts/1 :0.0 Fri Nov 2 13:35 - 13:49 (00:14)
    fortunep pts/3 :0.0 Fri Nov 2 11:29 - 13:26 (01:56)
    fortunep pts/1 :0.0 Fri Nov 2 11:28 - 13:26 (01:57)
    reboot system boot 3.2.0-31-generic Fri Nov 2 11:10 - 13:49 (02:38)
    fortunep pts/3 :0.0 Thu Nov 1 17:48 - 18:07 (00:19)
    fortunep pts/3 :0.0 Thu Nov 1 16:08 - 16:09 (00:00)
    fortunep pts/1 :0.0 Thu Nov 1 12:35 - 18:07 (05:31)

    wtmp begins Thu Nov 1 12:35:45 2012

    The rows marked with red are actually causing the problem.How to resolve this issue.I have tried using 'sed' to put the rows in a proper format.But its really difficult to segregate into different fields.I am looking for something like this

    Field1 Field2 Field3 Field4
    fortunep pts/1 :0.0 Thu Nov 1 12:35 - 18:07 (05:31)
    reboot system boot 3.2.0-31-generic Fri Nov 2 11:10 - 13:49 (02:38)
    fortunep pts/4 10.1.66.32 Fri Nov 9 12:13 - 12:23 (00:09)

    Please help me to achieve this.
     
    siddharth007, Nov 15, 2012 IP
  2. Anonimista

    Anonimista Peon

    Messages:
    12
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    3
    #2
    How do you want the data in your problematic rows split? What should go into which field?
     
    Anonimista, Nov 16, 2012 IP
  3. iceh

    iceh Greenhorn

    Messages:
    14
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #3
    LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table; Can be done from phpmyadmin or command line.
     
    iceh, Dec 20, 2012 IP