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 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 document 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 schreef:

> Each row in the text document 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

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.

stefaan.lhermitte@agr.kuleuven.ac.be wrote:
> Paul Lautman schreef:
>
>> Each row in the text document 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?

stefaan.lhermitte@agr.kuleuven.ac.be 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:
http://dev.mysql.com/doc/refman/5.0/en/news-5-0-x.html

Regards,
Bill K.

stefaan.lhermitte@agr.kuleuven.ac.be 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 parse the text file and execute the appropriate
INSERT statements to load the data.

Regards,
Bill K.