Link to home
Start Free TrialLog in
Avatar of wilson1000
wilson1000

asked on

MySQL LOAD DATA INFILE

Hello,

My we page is attempting to upload and insert bulk data from a txt file into a MySQL database.

I am running the following error:

- Data truncated for column 'ItemID' at row 1

Using this .txt file :

/**************************************************************/

1            New Book      This is new book      this is to see if we can successfully upload a text file to the database       100            image.jpg            0.9            
2                                                                  

/**************************************************************/

And also with the below PHP code.

What does this mean and how can it be rectified?

Many, many thanks
<?php require_once('Connections/connBookShop.php'); ?>
<?php mysql_select_db($database_connBookShop); ?>
<?php
if (isset($_FILES['dataFile_items'])) {
  if ($_FILES['dataFile_items']['size'] < 2000000) {
    if (is_uploaded_file($_FILES['dataFile_items']['tmp_name'])) {
      $sql = "LOAD DATA INFILE '".mysql_real_escape_string($_FILES['dataFile_items']['tmp_name'])."' INTO TABLE `items` FIELDS TERMINATED BY ';' ENCLOSED BY '\"' ESCAPED BY '\\\\' LINES TERMINATED BY '\\r\\n'";
      mysql_query($sql) or die(mysql_error());
    } else {
      $error = 'Upload failed.';
    }
  } else {
    $error = 'Upload exceeded size limit.';
  }
} else {
  $error = 'No file was uploaded.';
}
echo $error;
?>

Open in new window

Avatar of Kim Ryan
Kim Ryan
Flag of Australia image

Is this /**************************************************************/ actually in your first line? This could be the fault, you need just the data, so file should only contain

1            New Book      This is new book      this is to see if we can successfully upload a text file to the database       100            image.jpg            0.9            
2
Avatar of wilson1000
wilson1000

ASKER

Hi teraplane,

No I used that line and the one below to separate the text from the rest of my question.
OK the, you are using FIELDS TERMINATED BY ';' ENCLOSED BY '\"'
but your fields are terminated by spaces and not encsoed byquotes. You need to change the data, or the field definitions so that they match. The data should be like

1;  "New Book";"This is new book"  ... etc      
oh I see, can you show me how to change the code to tab delimited or comer delimited please  - I will be using excel to output the text file and I'm sure it won't output to the syntax above??

Thank you
OK, here is tab and comma delimited. SO long as your fields aredelimited, the ENCLOSED BY probably doesn't matter.
FIELDS TERMINATED BY '\t'
FIELDS TERMINATED BY ','
I amended the code as instructed bu t i get the following error now:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\\' LINES TERMINATED BY '\r\n'' at line 1
<?php
if (isset($_FILES['dataFile_items'])) {
  if ($_FILES['dataFile_items']['size'] < 2000000) {
    if (is_uploaded_file($_FILES['dataFile_items']['tmp_name'])) {
      $sql = "LOAD DATA INFILE '".mysql_real_escape_string($_FILES['dataFile_items']['tmp_name'])."' INTO TABLE `items` FIELDS TERMINATED BY '\t' ENCLOSED BY '\"' ESCAPED BY '\\\\' LINES TERMINATED BY '\\r\\n'";
      mysql_query($sql) or die(mysql_error());
    } else {
      $error = 'Upload failed.';
    }
  } else {
    $error = 'Upload exceeded size limit.';
  }
} else {
  $error = 'No file was uploaded.';
}
echo $error;
?>

Open in new window

Just say TERMINATED BY '\r\n'. You have an extra \ in there.
This is a toughy! That sorted it but this error is now looming:

Out of range value adjusted for column 'ItemCatID' at row 1

I really appreciate your help, thanks for this
ASKER CERTIFIED SOLUTION
Avatar of Kim Ryan
Kim Ryan
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I have realized that the issue may lay with a known MySQL bug.

However, the answers and advice given by teraplane was extremely useful and every comment was easy to understand.

The end result was that each column in my text file had to have a default value, NULL was not an option. The LOAD DATA INFILE works superbly, thanks again teraplane