...

View Full Version : Problems with LOAD DATA LOCAL INFILE ...



Endi
04-23-2004, 06:19 PM
Please, help me undersand what I am doing wrong here. The task seems to be pretty simple: I should load data from a local (.txt) file into my online database...
I know I can always use phpMyAdmin provided by ISP, click on "insert file" and it will work fine. Although I am not a programmer (but somehow ended up doing this as a part of my job), I don't like to use the code that I don't understand (besides, I would like to schedule this as a cron job):

If I go with phpMyAdmin the LOAD code is automatically created like this:

LOAD DATA LOCAL INFILE '/tmp/phpJtUQtM' INTO TABLE `parts2` FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' ;

the php code would be like:



$sql = 'LOAD DATA LOCAL INFILE \'/tmp/phpJtUQtM\' INTO TABLE `parts2` FIELDS TERMINATED BY \';\' ENCLOSED BY \'"\' ESCAPED BY \'\\\\\' LINES TERMINATED BY \'\\r\\n\'';


and that would work fine.

However, according to the MySQL Manual, the syntax is:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY '\t']
[[OPTIONALLY] ENCLOSED BY '']
[ESCAPED BY '\\' ]
]
[LINES
[STARTING BY '']
[TERMINATED BY '\n']
]
[IGNORE number LINES]
[(col_name,...)]

which makes me wondering why this wouldn't work:

LOAD DATA LOCAL INFILE 'newparts.txt' INTO TABLE `parts2` FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n';

all I get (as a query result in phpMyAdmin is "number of rows inserted: -1" ... :confused:

Don't worry, I will not quit my daily job LOL,

Please, help me.

Thanks.

Endi

litebearer
04-25-2004, 07:29 AM
could you post a line or two of your text file?

Endi
04-25-2004, 12:56 PM
there are hundreds of rows like this (fields delimited by ';') and it all works fine if I use phpMyAdmin...

;APPLIED MATERIALS;0010-00138;FRAME, UPPER AC DIST. ASSY;2 ;660.00;
;APPLIED MATERIALS;0010-00163;SHAFT, Z-AXIS ASSY.;1 ;137.50;
;APPLIED MATERIALS;0010-00171;ACTUATOR GATE VALVE;6 ;530.75;
;APPLIED MATERIALS;0010-00174;THROTTLE VALVE ACTUATOR ASSY.;1 ;1,340.40;
;APPLIED MATERIALS;0010-00198;REFLECTOR SOCKET ASSY.;5 ;68.75;
;APPLIED MATERIALS;0010-00215;PHASE MAG DETECTOR ASSY;12 ;881.27;
;APPLIED MATERIALS;0010-00218;MOTOR, WIPER ASSY;9 ;97.90;
;APPLIED MATERIALS;0010-00219;MOTOR;14 ;116.05;
;APPLIED MATERIALS;0010-00283;W GENEVA DRIVE ASSY;1 ;2,840.20;
;APPLIED MATERIALS;0010-00305;W GRIPPER ASSY 150MM;9 ;2,981.00;
;APPLIED MATERIALS;0010-00336;CLIP, LOWER ASSY;134 ;4.40;
;APPLIED MATERIALS;0010-00344;PEDESTAL 5\" for AMAT 8330;85 ;192.50;

and so on...

litebearer
04-25-2004, 03:32 PM
Ok. In case someone else comes along with a solution before I get back today (its Sunday Morning here 10:40 EST) use it.

When I get back I will see what solution I can put together.

Lite...

Endi
04-25-2004, 03:42 PM
Ok. In case someone else comes along with a solution before I get back today (its Sunday Morning here 10:40 EST) use it.

When I get back I will see what solution I can put together.

Lite...

Great. Thanks a lot.

Endi

litebearer
04-25-2004, 04:09 PM
Alomost forgot - can you post the fields for your db? And is there a unique identifier field?

Endi
04-25-2004, 04:33 PM
pid (primary key), pvend, pnum, pdes, pq, ppr

litebearer
04-25-2004, 09:24 PM
Ok..

1. my success with the load data was miserable

2. I did, however, put together a script for you to load the txt file via the INSERT.

3. There were a few things about your text file that bothered me...
(a) the leading semi-colon - most likely you have it there to "reserve" a place for your primary key -- I took steps in the script to handle that.

(b) some of your text file data includes slashes "\" -- also not necessary using the script provided, so I handled those.

(c) You used comma's in your text file price field -- I handled that by making the database price field a decimal field

(d) Your text file apparently used Windows line terminators - so I compensated for that.

4. I am sure a more experienced PHP'er (is that a new term?) could make the script considerably more efficient and elegant - but it does the job.


CAVEATS:

A -- make a backup of your text file BEFORE you run this script

B -- I tested this on my host but there are no guarantees

On With The Show


<?PHP
// put the file name into a variable

$what_file = "parts2.txt";

// put the entire txt file into an array

$all_lines = file($what_file);

// count the number of lines in the array and store it in a variable
$how_many_lines = count($all_lines);

// remove the leading semi-colon, the slashes,
// the trailing semi-colon, the windows line terminator,
// and the commas from each line

$i =0;
for ($i = 0; $i < $how_many_lines; $i ++){
$new_line_length = strlen($all_lines[$i]);
$xxx = $new_line_length - 1;
$new_line = substr($all_lines[$i], - $xxx);
$new_line = stripslashes($new_line);
$all_lines[$i] = $new_line;
}

$i =0;
for ($i = 0; $i < $how_many_lines; $i ++){
$new_line = $all_lines[$i];
$new_line_length = strlen($new_line);
$xxx = ($new_line_length - 3);
$new_line = substr($new_line, 0, $xxx);
$new_line = $new_line . "\n";
$all_lines[$i] = $new_line;
}

$i =0;
for ($i = 0; $i < $how_many_lines; $i ++){
$temp_array = explode (";", $all_lines[$i]);
$temp_price = $temp_array[4];
$where_is_it = 0;
if (strpos($temp_price, ",") > 0) {
$zzz = strlen($temp_price);
$yyy = strpos($temp_price, ",");
$zzz = $zzz - ($yyy + 1);
$left_side = substr($temp_price, 0, $yyy);
$right_side = substr($temp_price, - $zzz);
$new_price = $left_side . $right_side . ";";
$temp_array[4] = $new_price;
$temp_string = $temp_array[0] . ";" . $temp_array[1] . ";" . $temp_array[2];
$temp_string = $temp_string . ";" . $temp_array[3] . ";" . $temp_array[4];
}
}

// ------------------------------
// delete the txt file

unlink($what_file);

// create a new empty file

touch($what_file);

// open the text file in the append mode
// and add each line

$fp = fopen($what_file, 'a');
$i = 0;
for ($i = 0; $i < $how_many_lines; $i ++) {
$contents = $all_lines[$i];
fputs($fp, $contents);
}
fclose($fp);


// -----------------------------------
// prepare to add the file to
// the database

$user="your_user_name_here";
$password="your_password_here";
$database="your_database_name_here";

$conn = mysql_connect(localhost,$user,$password);
$result1 = mysql_select_db($database) or die( "Unable to select database");

if ($result1) {
echo ("successful connection");
} else {
echo ("no connection");
}

$i =0;
for ($i = 0; $i < $how_many_lines; $i ++){
$temp_array = explode (";", $all_lines[$i]);
$tpvend = $temp_array[0];
$tpnum = $temp_array[1];
$tpdes = $temp_array[2];
$tpq = $temp_array[3];
$tppr = $temp_array[4];

$sql = "INSERT INTO parts2 (pvend,pnum,pdes,pq,ppr) VALUES ('$tpvend','$tpnum','$tpdes','$tpq','$tppr')";
$result = mysql_query($sql);
}

?>

Hope this helps. :)

Lite...

Endi
04-26-2004, 03:31 PM
Thank you, but...

The text file is sitting on my computer. The INSERT statement is on one of the pages on my website (so, on the server). I can't do fopen on the local file from Internet, can I ?

I would probably have to upload it to my server first and that would happen several times a day, which is what I am trying to avoid (for many reasons that I wouldn't bother you with right now).

Thank you for your efforts.

Endi



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum