Professional Web Applications Themes

Complex LOAD DATA INFILE - MySQL

Dear mysql-ians, I am using mysql 5.0 and I want to load a huge txt-file in my database. My text file (file.txt) looks like: col1 col2 col3 ... col200 col1 col2 col3 ... col200 .... col1 col2 col3 ... col200 I now want it to import in a table t1 with two columns (col_nr, col_val) where col_nr is the number of column (e.g. 2 for col2, 46 for col46) and col_val are the effective values in my txt-file at the different columns. The problem is that col_nr is not in the "file.txt" so I have to assign based on the ...

  1. #1

    Default Complex LOAD DATA INFILE

    Dear mysql-ians,

    I am using mysql 5.0 and I want to load a huge txt-file in my database.
    My text file (file.txt) looks like:

    col1 col2 col3 ... col200
    col1 col2 col3 ... col200
    ....
    col1 col2 col3 ... col200

    I now want it to import in a table t1 with two columns (col_nr,
    col_val) where col_nr is the number of column (e.g. 2 for col2, 46 for
    col46) and col_val are the effective values in my txt-file at the
    different columns. The problem is that col_nr is not in the "file.txt"
    so I have to assign based on the field number.

    I am looking at the LOAD DATA INFILE command, but in the help file I
    did not find an answer to my question. I was hoping to do it with:

    LOAD DATA INFILE 'file.txt'
    INTO TABLE t1
    (col_val)
    SET col_nr = "how do I do this";

    Does anyone has any suggestions or tips to do it differently (with php
    perhaps)?

    Thanks in advance!

    Stef

    stefaan.lhermitte@agr.kuleuven.ac.be Guest

  2. #2

    Default Re: Complex LOAD DATA INFILE

    [email]stefaan.lhermitteagr.kuleuven.ac.be[/email] wrote:
    > Dear mysql-ians,
    >
    > I am using mysql 5.0 and I want to load a huge txt-file in my
    > database. My text file (file.txt) looks like:
    >
    > col1 col2 col3 ... col200
    > col1 col2 col3 ... col200
    > ...
    > col1 col2 col3 ... col200
    >
    > I now want it to import in a table t1 with two columns (col_nr,
    > col_val) where col_nr is the number of column (e.g. 2 for col2, 46 for
    > col46) and col_val are the effective values in my txt-file at the
    > different columns. The problem is that col_nr is not in the "file.txt"
    > so I have to assign based on the field number.
    >
    > I am looking at the LOAD DATA INFILE command, but in the help file I
    > did not find an answer to my question. I was hoping to do it with:
    >
    > LOAD DATA INFILE 'file.txt'
    > INTO TABLE t1
    > (col_val)
    > SET col_nr = "how do I do this";
    >
    > Does anyone has any suggestions or tips to do it differently (with php
    > perhaps)?
    >
    > Thanks in advance!
    >
    > Stef
    Each row in the text doent has different values for the column number,
    yes? So the table will have lots of rows with col_nr = 3 and different
    values in col_var? Also how are the columns delimited in the text file?


    Paul Lautman Guest

  3. #3

    Default Re: Complex LOAD DATA INFILE

    Paul Lautman schreef:
    > Each row in the text doent has different values for the column number,
    > yes? So the table will have lots of rows with col_nr = 3 and different
    > values in col_var?
    Indeed my table has 270 columns and the values for every field for
    every row are different. Below you can find a small extract of my
    file.txt (with only 3 columns and 8 rows).

    38 61 1
    35 64 1
    35 64 1
    41 59 0
    39 61 0
    42 58 0
    28 72 0
    36 64 0
    > Also how are the columns delimited in the text file?
    My text-file is organised in a fixed length fomat for every field (as
    you can see in the extract).

    I also see that the SET command is only valid from MySQL 5.0.3 and I
    just donwloaded 5.0.21 so I assume the SET will not work anyway. Do you
    have other suggestions?

    For your information: my text file contains al lot of info (270 fields
    X 2M records).

    Thanks in advance,
    Stef

    stefaan.lhermitte@agr.kuleuven.ac.be Guest

  4. #4

    Default Re: Complex LOAD DATA INFILE

    Just to clarify. As I understand it, you want to take the file and
    rearrange it like this (I'm using your sample data):

    COL_NR | VALUE
    1 | 38
    1 | 35
    1 | 35
    1 | 41
    1 | 39
    1 | 42
    1 | 28
    1 | 36
    2 | 61
    2 | etc, etc.

    So maybe your table should have a 3rd column, or, more precisely a
    '1st' column which will hold the key to the table - like this:

    id | col_nr | value
    1 | 1 | 38
    2 | 1 | 35
    3 | 1 | 35
    4 | 1 | etc, etc.

    As for getting the data into the table, I'm a bit stuck. I think that
    the PHP solution might be the way to go - with a loop that counts to
    270 for instance.

    strawberry Guest

  5. #5

    Default Re: Complex LOAD DATA INFILE

    [email]stefaan.lhermitteagr.kuleuven.ac.be[/email] wrote:
    > Paul Lautman schreef:
    >
    >> Each row in the text doent has different values for the column
    >> number, yes? So the table will have lots of rows with col_nr = 3 and
    >> different values in col_var?
    >
    > Indeed my table has 270 columns and the values for every field for
    > every row are different. Below you can find a small extract of my
    > file.txt (with only 3 columns and 8 rows).
    >
    > 38 61 1
    > 35 64 1
    > 35 64 1
    > 41 59 0
    > 39 61 0
    > 42 58 0
    > 28 72 0
    > 36 64 0
    >
    >> Also how are the columns delimited in the text file?
    >
    > My text-file is organised in a fixed length fomat for every field (as
    > you can see in the extract).
    >
    > I also see that the SET command is only valid from MySQL 5.0.3 and I
    > just donwloaded 5.0.21 so I assume the SET will not work anyway. Do
    > you have other suggestions?
    >
    > For your information: my text file contains al lot of info (270 fields
    > X 2M records).
    >
    > Thanks in advance,
    > Stef
    When I used to need to do this sort of things back when I worked for IBM, I
    would always turn to PIPELINEs to change that file into one that would load
    into the database (actually if putting into DB/2 I would use the PIPELINEs
    DB/2 device driver to maipulate the data and feed it straight into the
    database using INSERT statements created in the PIPELINE).

    Datamystic's .com TextPipe product enables you to do this on a PC.

    However, even without PIPELINEs, I would still be tempted to preprocess your
    file using whatever came to hand (REXX, VB, VBA, PHP, PERL, ...) to make it
    look like:

    1,38
    1,35
    1,35
    1,41
    1,39
    1,42
    1,28
    1,36
    2,61
    2,64
    2,64
    2,59
    2,61
    2,58
    2,72
    2,64
    3,1
    3,1
    3,1
    3,0
    3,0
    3,0
    3,0
    3,0


    Which could then be loaded simply into the database. Is every field the same
    length, or do they vary and is there always at least one space between the
    fields, or do some run into each other?


    Paul Lautman Guest

  6. #6

    Default Re: Complex LOAD DATA INFILE

    [email]stefaan.lhermitteagr.kuleuven.ac.be[/email] wrote:
    > I also see that the SET command is only valid from MySQL 5.0.3 and I
    > just donwloaded 5.0.21 so I assume the SET will not work anyway. Do you
    > have other suggestions?
    5.0.21 is a later release than 5.0.3, and should be a superset of the
    features in 5.0.3. See the sequence of releases here:
    [url]http://dev.mysql.com/doc/refman/5.0/en/news-5-0-x.html[/url]

    Regards,
    Bill K.
    Bill Karwin Guest

  7. #7

    Default Re: Complex LOAD DATA INFILE

    [email]stefaan.lhermitteagr.kuleuven.ac.be[/email] wrote:
    > Dear mysql-ians,
    >
    > I am using mysql 5.0 and I want to load a huge txt-file in my database.
    > My text file (file.txt) looks like:
    >
    > col1 col2 col3 ... col200
    > col1 col2 col3 ... col200
    > ...
    > col1 col2 col3 ... col200
    >
    > I now want it to import in a table t1 with two columns (col_nr,
    > col_val) where col_nr is the number of column (e.g. 2 for col2, 46 for
    > col46) and col_val are the effective values in my txt-file at the
    > different columns. The problem is that col_nr is not in the "file.txt"
    > so I have to assign based on the field number.
    One solution would be to do it in two steps. Create a new table with
    200 columns, load the data from the file, and then use SQL to copy the
    values fom that table to the two-column destination table.

    CREATE TABLE t1_wide( col1 CHAR(3), col2 CHAR(3), col3 CHAR(3), ...
    col200 CHAR(3));
    LOAD DATA INFILE INTO TABLE t1_wide FIELDS TERMINATED BY ' ';
    INSERT INTO t1 SELECT 1, col1 FROM t1_wide;
    INSERT INTO t1 SELECT 2, col2 FROM t1_wide;
    INSERT INTO t1 SELECT 3, col3 FROM t1_wide;
    ....
    INSERT INTO t1 SELECT 200, col200 FROM t1_wide;

    Another solution would be to write a custom application in your favorite
    programming language to p the text file and execute the appropriate
    INSERT statements to load the data.

    Regards,
    Bill K.
    Bill Karwin Guest

Similar Threads

  1. LOAD DATA INFILE
    By Luca in forum MySQL
    Replies: 4
    Last Post: July 11th, 12:35 PM
  2. LOAD DATA INFILE problem
    By howachen@gmail.com in forum MySQL
    Replies: 3
    Last Post: June 10th, 05:56 PM
  3. Load Data Infile question
    By bradfordh@gmail.com in forum MySQL
    Replies: 3
    Last Post: March 23rd, 04:53 AM
  4. help : LOAD DATA LOCAL INFILE
    By Dave in forum PHP Development
    Replies: 3
    Last Post: October 5th, 12:22 AM
  5. LOAD DATA LOCAL INFILE and Perl DBI/DBD
    By Jami Bradley in forum PERL Modules
    Replies: 1
    Last Post: June 25th, 03:08 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139