Jump to content

Deliminator INSERT


dreamwest

Recommended Posts

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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()); 

Link to comment
Share on other sites

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

?>

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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());
?>

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.