Re: [PHP-DB] MySQL: Load Data Infile

[dpgirago]

>> 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

Re: [PHP-DB] MySQL: Load Data Infile

[dpgirago]

>Am Dienstag, 16. Mai 2006 17:40 schrieb [EMAIL PROTECTED]:
>> 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: [PHP-DB] MySQL: Load Data Infile

[Oliver Block]

Am Dienstag, 16. Mai 2006 17:40 schrieb [EMAIL PROTECTED]:
> 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

[PHP-DB] MySQL: Load Data Infile

[dpgirago]

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: [PHP-DB] mySQL LOAD DATA INFILE

[Russ Michell]

2 things:

>  mysql_query($QueryInsert);
>  print mysql_error();

Should read: 

$query = mysql_query($QueryInsert) or die(mysql_error());

and:

>> It returns "Can't get stat of '/www/publico2/tableData0TMRRy' (Errcode: 13)".

what exactly is: "tableData0TMRRy"?? if it should be a file, it should be called 
'myfile.txt' (with 
a file suffix)

HTH
Russ

On Tue, 6 Nov 2001 14:56:00 +0200 =?iso-8859-1?Q?Niklas_Lamp=E9n?= 
<[EMAIL PROTECTED]> wrote:

> I have this code:
>  
>  $dir = getcwd();
>  
>  $fpName = tempnam($dir, "tableData");
>  $fp = fopen($fpName, "w");
>  fputs($fp, $strData);
>  fclose($fp);
>  
>  $QueryInsert = "
>   LOAD DATA INFILE '$fpName'
>INTO TABLE feStatics
>FIELDS TERMINATED BY '|'
>LINES TERMINATED BY '\n'
>  ";
>  
>  mysql_query($QueryInsert);
>  print mysql_error();
>  
>  unlink($fpName);
> ?>
>  
> It returns "Can't get stat of '/www/publico2/tableData0TMRRy' (Errcode:
> 13)".
>  
> I'd put this file into mySQL's database dir, but I have no access there.
> Any ideas how to do this? Inserting file row by row would take like
> eternity, so that is not a good idea.
>  
>  
> Niklas

#---#

  "Believe nothing - consider everything"   
  
  Russ Michell
  Anglia Polytechnic University Webteam
  Room 1C 'The Eastings' East Road, Cambridge
  
  e: [EMAIL PROTECTED]
  w: www.apu.ac.uk/webteam

  www.theruss.com

#---#


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

[PHP-DB] mySQL LOAD DATA INFILE

[Niklas Lampén]

I have this code:
 

 
It returns "Can't get stat of '/www/publico2/tableData0TMRRy' (Errcode:
13)".
 
I'd put this file into mySQL's database dir, but I have no access there.
Any ideas how to do this? Inserting file row by row would take like
eternity, so that is not a good idea.
 
 
Niklas