MySQL: Load Data Infile

Greets,

I'm trying to use the load data infile syntax within a php script. The data
is in csv format. I was under the impression that I could reorder the
fields during the insert, such as:
"load data infile 'currentData.txt' into table(fieldName2, fieldName1)
fields terminated by ','"; The stuff in the parentheses causes an error
but without the parens, it's inserts OK.
I'm sure I've done this in the past, but is was long ago.

Anybody have an insight into this?

David

(yes, it's more MySQL than PHP related, but I'm not currently subscribed to
that list...)

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: MySQL: Load Data Infile

Am Dienstag, 16. Mai 2006 17:40 schrieb dpgirago@mdanderson.org:
> Greets,
>
> I'm trying to use the load data infile syntax within a php script. The data
> is in csv format. I was under the impression that I could reorder the
> fields during the insert, such as:
> "load data infile 'currentData.txt' into table(fieldName2, fieldName1)
> fields terminated by ','"; The stuff in the parentheses causes an error
> but without the parens, it's inserts OK.
> I'm sure I've done this in the past, but is was long ago.
>
> Anybody have an insight into this?
>
> David
>
> (yes, it's more MySQL than PHP related, but I'm not currently subscribed to
> that list...)

Sometimes a look into the manual is helpful!


By default, when no column list is provided at the end of the LOAD DATA
INFILE statement, input lines are expected to contain a field for each table
column. If you want to load only some of a table's columns, specify a column
list:

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);

You must also specify a column list if the order of the fields in the input
file differs from the order of the columns in the table. Otherwise, MySQL
cannot tell how to match input fields with table columns.




Best Regards,

Oliver

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: MySQL: Load Data Infile

>Am Dienstag, 16. Mai 2006 17:40 schrieb dpgirago@mdanderson.org:
>> I'm trying to use the load data infile syntax within a php script. The
data
>> is in csv format. I was under the impression that I could reorder the
>> fields during the insert, such as:
>> "load data infile 'currentData.txt' into table(fieldName2, fieldName1)
>> fields terminated by ','"; The stuff in the parentheses causes an error
>> but without the parens, it's inserts OK.
>> I'm sure I've done this in the past, but is was long ago.
>>
>> Anybody have an insight into this?
>>
>> David

> Sometimes a look into the manual is helpful!
>
>
> By default, when no column list is provided at the end of the LOAD DATA
> INFILE statement, input lines are expected to contain a field for each
table
> column. If you want to load only some of a table's columns, specify a
column
> list:
>
> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);
>
> You must also specify a column list if the order of the fields in the
input
> file differs from the order of the columns in the table. Otherwise, MySQL

> cannot tell how to match input fields with table columns.
>
>
>
>
> Best Regards,
>
> Oliver

Ah, thanks for the nudge, Oliver!

I had the manual open but I'm used to the 4.0.** series, and now I realize
this is on an older 3.23.58 version.
I had forgotten about this legacy system.

Got the answer, thanks.

David

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: MySQL: Load Data Infile

>> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);

Syntax isn't quite correct with field and line terminators specified.

>>
>> You must also specify a column list if the order of the fields in the
input
>> file differs from the order of the columns in the table. Otherwise,
MySQL
>> cannot tell how to match input fields with table columns.
>>
>>
>>
>>
>> Best Regards,
>>
>> Oliver

> Ah, thanks for the nudge, Oliver!
>
> I had the manual open but I'm used to the 4.0.** series, and now I
realize this is on an older 3.23.58 version.
> I had forgotten about this legacy system.

For the archives, or any currently interested listers.

The MySQL manual for the 3, 4.0, and 4.1 series does not have an example
for specifying a column list with field and line terminators that I could
find, but this syntax works:

$query = "load data infile 'data.tx' into table tablename fields terminated
by ',' lines terminated by '\r\n' (fld2, fld1, fld3)";

It's counterintuitive I think to have the field list after the terminator
specifications instead of following the tablename, but that's the only way
I got it to work.

David

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php