Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New Coder
    Join Date
    Mar 2004
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problems with LOAD DATA LOCAL INFILE ...

    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:

    PHP Code:
    $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" ...

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

    Please, help me.

    Thanks.

    Endi

  • #2
    Regular Coder
    Join Date
    Apr 2004
    Posts
    298
    Thanks
    0
    Thanked 23 Times in 23 Posts
    could you post a line or two of your text file?

  • #3
    New Coder
    Join Date
    Mar 2004
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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...

  • #4
    Regular Coder
    Join Date
    Apr 2004
    Posts
    298
    Thanks
    0
    Thanked 23 Times in 23 Posts
    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...

  • #5
    New Coder
    Join Date
    Mar 2004
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by litebearer
    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

  • #6
    Regular Coder
    Join Date
    Apr 2004
    Posts
    298
    Thanks
    0
    Thanked 23 Times in 23 Posts
    Alomost forgot - can you post the fields for your db? And is there a unique identifier field?

  • #7
    New Coder
    Join Date
    Mar 2004
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    pid (primary key), pvend, pnum, pdes, pq, ppr

  • #8
    Regular Coder
    Join Date
    Apr 2004
    Posts
    298
    Thanks
    0
    Thanked 23 Times in 23 Posts

    Smile

    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 Code:
    <?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_line0$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_price0$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...
    Last edited by litebearer; 04-25-2004 at 09:27 PM.

  • #9
    New Coder
    Join Date
    Mar 2004
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •