Deliminator INSERT

[dreamwest]

I have a list of about 20,000 descriptions and categories like this:

 

Description is here|Category is here

 

 

How can i use this to INSERT values into 2 separate columns named "category" and "description"

[abdfahim]

I dont understand. Do you have those 20K records on a CSV/txt file?

[dreamwest]

I dont understand. Do you have those 20K records on a CSV/txt file?

Yes. A text file.

[corbin]

http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html

 

Would be the easiest way.

 

 

 

Else, just write a short PHP script:

 

 

<?php
mysql_connect();
mysql_select_db();

$lines = file('file.txt');
foreach($lines as $line) {
    $e = explode('|', $line);
    $e[0] = mysql_real_escape_string($e[0]);
    $e[1] = mysql_real_escape_string($e[1]);
    mysql_query("INSERT INTO table VALUES ('$e[0]', '$e[1]');");
}

 

 

 

Making less queries with more VALUE clauses would be better speed wise, but that would take more effort coding wise ;p.

[dreamwest]

http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html

 

Would be the easiest way.

 

 

 

Else, just write a short PHP script:

 

 

<?php
mysql_connect();
mysql_select_db();

$lines = file('file.txt');
foreach($lines as $line) {
    $e = explode('|', $line);
    $e[0] = mysql_real_escape_string($e[0]);
    $e[1] = mysql_real_escape_string($e[1]);
    mysql_query("INSERT INTO table VALUES ('$e[0]', '$e[1]');");
}

 

 

 

Making less queries with more VALUE clauses would be better speed wise, but that would take more effort coding wise ;p.

 

I tried this bjut it didnt work:

 

<?php

mysql_connect("localhost", "user", "pass") or die(mysql_error());
mysql_select_db("name") or die(mysql_error());

echo "connected";

$lines = file('file.txt');
foreach($lines as $line) {
    $e = explode('|', $line);
    $e[0] = mysql_real_escape_string($e[0]);
    $e[1] = mysql_real_escape_string($e[1]);
    $e[2] = mysql_real_escape_string($e[1]);
    mysql_query("INSERT INTO  VALUES ('$e[0]', '$e[1]'), '$e[2]') ;");
}

echo "done";

?>

 

I created a file called "file.txt" and uploaded it to the same directory.

 

The contents of file.txt look like this:

 

category_name1|category_url1|description1

category_name2|category_url2|description2

category_name3|category_url3|description3

etc....

 

 

And the columns of the table are:

 

categories, category_url, and description

 

Can anyone see my errror??

[Mchl]

I can. There's no table specified in your query.

 

Try this (put your table name instead of 'table' in INSERT INTO statement).

 

mysql_connect("localhost", "user", "pass") or die(mysql_error());
mysql_select_db("name") or die(mysql_error());

echo "connected";

$query = "INSERT INTO table VALUES ";

$lines = file('file.txt');
foreach($lines as $line) {
    $e = explode('|', $line);
    $e[0] = mysql_real_escape_string($e[0]);
    $e[1] = mysql_real_escape_string($e[1]);
    $e[2] = mysql_real_escape_string($e[2]);
    $query .= "('{$e[0]}', '{$e[1]}', '{$e[2]}'),";
}

$query = substr($query,0,-1);
mysql_query($query) or die(mysql_error()." $query");

echo "done";

[dreamwest]

I can. There's no table specified in your query.

 

Try this (put your table name instead of 'table' in INSERT INTO statement).

 

Your right  must have overlooked it.

 

I ran the script but im getting an error:

 

Column count doesn't match value count at row 1 INSERT INTO gallery_import VALUES

 

Any idea whats going on??

[Mchl]

There was an error in the script, and I edited it since. Try again.

[dreamwest]

Sorry the error i was getting was:

 

Column count doesn't match value count at row 1 INSERT INTO import VALUES

[Mchl]

How many columns you have in your table?

[dreamwest]

5 the final column is "download":

 

This is used by another script, its only values can be 0 or 1. But for the import the value can be 0 for all rows

 

and the first column is the id eg; 1,2,3,4 etc...

 

Heres the TABLE create script i used:

 

mysql_query("CREATE TABLE gallery_import(
id INT NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(id),
  categories TEXT,
  category_url TEXT, 
  description TEXT,
  download TINYINT NOT NULL)")
or die(mysql_error()); 

[Mchl]

Change

 

$query .= "('{$e[0]}', '{$e[1]}', '{$e[2]}'),";

 

to

 

$query .= "('{$e[0]}', '{$e[1]}', '{$e[2]}', 0),";

 

then.

[dreamwest]

yes thats it.

 

But i still need to insert the id, it needs to count up from 1 

[Mchl]

$query .= "(NULL, '{$e[0]}', '{$e[1]}', '{$e[2]}', 0),";

[dreamwest]

HEY it works!

 

Thanks for your help i couldn't have done it without you

[dreamwest]

got a small problem with this script, it works great but it imports a "return" space after the end of every line thus rendering it useless

 

Example of import.txt:

 

category 1|description 1| category url1
category 2|description 2| category url2

 

when you look at ... category url1 and category url2 it has white space after it, even though the import.txt doesnt have any whitespace afetr each line

 

How can i get rid of this whitespace afer each line?

 

<?php
mysql_connect("localhost", "user", "Pass") or die(mysql_error());
mysql_select_db("name") or die(mysql_error());


$query = "INSERT INTO import VALUES ";

$lines = file('file.txt');
foreach($lines as $line) {
    $e = explode('|', $line);
    $e[0] = mysql_real_escape_string($e[0]);
    $e[1] = mysql_real_escape_string($e[1]);
    $e[2] = mysql_real_escape_string($e[2]);
    $query .= "('NULL', '{$e[0]}', '{$e[1]}', '{$e[2]}', '0'),";
}

$query = substr($query,0,-1);
mysql_query($query) or die(mysql_error()." $query");

echo "done";

?>

[corbin]

either use trim() or:

 

 

file('blah', FILE_IGNORE_NEW_LINES);

[dreamwest]

either use trim() or:

 

 

file('blah', FILE_IGNORE_NEW_LINES);

 

Like this??:

 

$lines = file('file.txt');
foreach trim(($lines as $line)) {
    $e = explode('|', $line);
    $e[0] = mysql_real_escape_string($e[0]);
    $e[1] = mysql_real_escape_string($e[1]);
    $e[2] = mysql_real_escape_string($e[2]);
    $query .= "('NULL', '{$e[0]}', '{$e[1]}', '{$e[2]}', '0'),";
}

$query = substr($query,0,-1);
mysql_query($query) or die(mysql_error()." $query");

echo "done";

[abdfahim]

use phpmyadmin .. then import .. thn choose CSV format ... then write | in delimeter box.

[dreamwest]

use phpmyadmin .. then import .. thn choose CSV format ... then write | in delimeter box.

 

Yes that worked thanks!

 

but im still interested in finishing this script, itll be handy to have

[abdfahim]

I have written one code long time back for my own purpose. I can share that here. Though I have many kinds of security measures inside the code to handle wrong entry or wrong file structure or wrong table structure, I delete those portion for the sake of simplicity when I am posting the code.

<?php
      include ("config.php"); // DB Connection
      if(!ini_set("max_execution_time", "600")){ //To allow this script run long
          throw new Exception("Failed to set execution time");
      }
      
      $fileup="mydata.txt"; //FILE NAME
      
      $totdt=0;
      $query="REPLACE INTO `MyTable` "; // I use REPLACE instead of INSERT to avoid duplicate row. For that you have to have KEY defined
      if (!$file = fopen($fileup,"r")) {
            echo "Cannot open file.";
            exit;
      }
      $a=fgets($file,4096);
      $line = explode("|",$a);
      $totalcol=count($line);
      
      //////////////////// If You Have the column name in your text file /////////////////////////////////
      $query .= "(";
      for($i=0;$i<($totalcol-1);$i++){
            if(!get_magic_quotes_gpc()) {
                  $temp = addslashes($line[$i]);
            }else{
                  $temp = $line[$i];
            }
            $query .= "`".trim($temp)."`,";
      }      
      $query .= "`".trim($line[$totalcol-1])."`)";
      ////////////////////////////////////////////////////////////////////////////////////////////////////
      
      $values = " VALUES";
      while(!feof($file)){
            $a=fgets($file,4096) ;
            $line=array();
            $line = explode("|",$a);
            for($i=0;$i<($thisline);$i++){
                  if(!get_magic_quotes_gpc()) {
                        $values .= ",'".trim(addslashes($line[$i]))."'";
                  }else{
                        $values .= ",'".trim($line[$i])."'";
                  }
            }
            $values = ",(".substr($values, 1).")";
      }
      $qry = $query.substr($values, 1).";";
      mysql_query ($qry) or die(mysql_error());
?>

[corbin]

either use trim() or:

 

 

file('blah', FILE_IGNORE_NEW_LINES);

 

Like this??:

 

$lines = file('file.txt');
foreach trim(($lines as $line)) {
    $e = explode('|', $line);
    $e[0] = mysql_real_escape_string($e[0]);
    $e[1] = mysql_real_escape_string($e[1]);
    $e[2] = mysql_real_escape_string($e[2]);
    $query .= "('NULL', '{$e[0]}', '{$e[1]}', '{$e[2]}', '0'),";
}

$query = substr($query,0,-1);
mysql_query($query) or die(mysql_error()." $query");

echo "done";

 

 

No x.x.  You would use trim on $line.

 

foreach ($lines as $line) {

    $e = explode('|', trim($line));