CODEWITHSUNDEEP
phpmysqlsqldatabasephpmyadmin
Mo.
Mo.

Reputation: 27445

How to import a text file to MySQL direct or through PHP

I need to import my text file to MySQL which has only one field. It is like this:

enter image description here

I could also do this with PHP.

Upvotes: 1

Views: 12732

Answers (5)

Shiplu Mokaddim
Shiplu Mokaddim

Reputation: 57640

You can use Mysql LOAD DATA LOCAL INFILE syntax

LOAD DATA LOCAL INFILE '/path/to/file.txt' 
    INTO TABLE 'table1'
    LINES TERMINATED BY '\n'

For this, make sure Mysql has access to /path/to/file.txt. Also the user who is executing the query must have FILE privilege.

With Pure PHP its easy. Read the file, build the query, execute it. You need to build the query so that you dont end up looping query which is slow.

$data = file("/path/to/file.txt", FILE_SKIP_EMPTY_LINES);

// make sure you have valid database connection prior to this point.
// otherwise mysql_real_escape_string won't work
$values = "('". implode("'), ('", array_map('mysql_real_escape_string', $data)). "')";

$query = "INSERT INTO `TABLE1` (`COLUMN1`) VALUES $values";

// Now just execute the query once.
mysql_query($query);

Upvotes: 3

rackemup420
rackemup420

Reputation: 1567

<?php
$array = preg_split("[\r\n]+", file_get_contents("path/to/file.txt"))

foreach ($array as $line) {
mysql_query("INSERT INTO `dbname` (colmun1) VALUES ('$line')");
}
?>

Always sanitize too !

Upvotes: 0

Vyktor
Vyktor

Reputation: 20997

Using csv import in phpMyAdmin

phpMyAdmin supports CSV files import, look at this article for more details (first result after: phpMyAdmin csv)

Using mysqlimport

Mysql provides CLI application, mysqlimport, example usage (again CSV):

mysqlimport --fields-optionally-enclosed-by='"' --fields-terminated-by=, \
    --lines-terminated-by="\r\n" --user=YOUR_USERNAME --password \
    YOUR_DATABASE YOUR_TABLE.csv

Mysql LOAD DATA

Mysql itself (client, query) supports LOAD DATA INFILE command, example syntax:

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

Build sql query with php (and insert manually)

You will parse text file in php and output will be one large INSERT statement (this will be useful when/if you cannot connect to mysql from the server where you're running the script:

// This will escape values correctly
function escapeValues( &$val){
    // mysql_real_escape_string is not an option (remote server not accessible
    // in this case)
    return "'" . addslashes( $val) . "'"; 
}

$fp = fopen( $filename, 'r') or die( 'Cannot open');
$result = array();
while( $row = fgets( $fp)){
    $values = explode( '/', $row);
    array_walk( $values, 'escapeValues');
    $results[] = '(' . implode( ', ', $values) . ')';
}

fclose( $fp);

if( !count( $results){
   die();
}

echo 'INSERT INTO tableName (col1, col2, ... colN) VALUES ';
echo implode( ",\n", $results);
echo "\n";

Direct connection and direct import

This should be the best approach for you.

$conn = mysql_connect( ...) or die(...);
mysql_select_db(...);

// Now we need to build small class which will allow us escape values properly
// mysql_escape_string is DEPRECATED and mysql_real_escape_string
// requires connection parameter
// Ps: choose better name
class CallbackHack {
    public $connection = null;
    public function escapeValue( &$val){
        $val = "'" . mysql_real_escape_string( $val, $this->connection) . "'";
    }
}
$hack = new CallbackHack();
$hack->connection = $conn;

$fp = fopen( $filename, 'r') or die( 'Cannot open');
mysql_query( 'BEGIN TRANSACTION;'); // Increases insert performance for InnoDb
while( $row = fgets( $fp)){
    $values = explode( '/', $row);
    array_walk( $values, array( $hack, 'escapeValue'));
    $sql = 'INSERT INTO tableName (col1, col2, ... colN) VALUES (' .
            implode( ', ', $values) . ');';
    mysql_query( $sql);
}

mysql_query( 'COMMIT;'); // Make sure it will run
fclose( $fp);

Upvotes: 1

user769889
user769889

Reputation: 376

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);


$file = fopen("welcome.txt", "r") or exit("Unable to open file!");
    //Output a line of the file until the end is reached

    while(!feof($file))
      {

$data = mysql_real_excape_string(fgets($file));
mysql_query("INSERT INTO Persons (filedata)
VALUES ($data)");
//make sure above query is right according to your table structure
      }
    fclose($file);

mysql_close($con);
?>

Upvotes: 0

MrJ
MrJ

Reputation: 1938

Why not use some regex to basically split it up by new lines, ie.

$array = preg_split("/[\r\n]+/", file_get_contents("path/to/file.txt"));

and then do a foreach loop, ie:

foreach($array as $line){
   // insert into database
}

Then all you need to do is fill the line above where you insert it into the correct field in the database, line by line - but please sanitize each line, just so you don't inject the database with anything bad!

Upvotes: 1

Related Questions