inserting data into mysql database from a tab delimited file

mystrymaster

Alright so I have all the info from a lotus notes database exported into a tab delimited form and I need to import that into a mysql database - how would I go about doing that?

I am just getting into the world of database administration but have familiarity with sql

shawon22

use the explode() function.

example.

$line1 = "a,b,c,d,e,f";

$chr = explode(",", $line1);

for($i=0; $i<count($chr); $i++)
 INSERT INTO TABLE(COLUMN1) VALUES ($chr[$i]);

mystrymaster

I'm sorry here but I still don't understand. Could you clarify a little more?

mahers

Do you mean performing the operation from the command line?

If so http://www.mysql.com/doc/en/mysqlimport.html

Mark_Nordstrom

<?php
$result = mysql_db_query('databasename' ,"LOAD DATA LOCAL INFILE '$form_data' INTO TABLE tablename FIELDS TERMINATED BY '	' LINES TERMINATED BY '\r\n'") or die(mysql_error());
?>

the form is simple

<form method="post" action="process.php" enctype="multipart/form-data">
  <p> 
    <input type="file" name="form_data" size="40">
  <p> 
    <input type="submit" name="submit" value="submit">
</form>

This should work save one as form.php and other as process.php just browse for the tab delimited file thru the form. Make sure you put the mysql connect info in the process.php page

mystrymaster

Very cool I'll try that - thanks

Mark_Nordstrom

let me know if you have any problems.

mystrymaster

So here is my page

 
<?php include 'includes/dbase.php' ?>

<?php
$result = mysql_db_query('aaadispatch' ,"LOAD DATA LOCAL INFILE '$form_data' INTO TABLE carInfo FIELDS TERMINATED BY '    ' LINES TERMINATED BY '\r\n'") or die(mysql_error());
?>

and this is the error that I am getting

The used command is not allowed with this MySQL version

So I try to use the following statement from the MySql Control center

LOAD DATA LOCAL INFILE 'data/carinfo.txt' INTO TABLE carInfo FIELDS TERMINATED BY '    ' LINES TERMINATED BY '\r\n'"

and it goes in and creates the 300 or so records but does not insert a thing in any of the other fields - I know it is inserting the records as I have an ID field that auto increments any help?

Mark_Nordstrom

nope no idea why. it works fine for me.

muinej

try this with an html form that has a textarea named "forminput". copy and paste from your tab delimited text file.

... works for me

$lines = explode("\n", $forminput);
$c = 0;
$r = 1;
while ($r <= count($lines)) {
$fields = explode("\t", $lines[$c]);
$var1 = $fields[0];
$var2 = $fields[1];
$var3 = $fields[2];

$query="INSERT INTO tablename VALUES '$var1', '$var2', '$var3')";
mysql_query($query);
$result = mysql_insert_id();

echo "<br>".$result." - ".$var1."\n";
$c++;
$r++;

}

fac

code: LOAD DATA LOCAL INFILE 'data/carinfo.txt' INTO TABLE carInfo FIELDS TERMINATED BY ' ' LINES TERMINATED BY '\r\n'"

Shouldn't it be: fields terminated by '\t' since it is a tab delimited file?

Mark_Nordstrom

maybe yah.

mystrymaster

Well I eventually went into excell and saved it as a CSV and imported that way. but I have more so I will try again.

Norman_Graham

The use of the 'LOCAL' attribute only works if the file is in a particular place (I can't remember where). If it's in the wrong spot, you get a bad-command error. I recommend never using the 'LOCAL' specification. Use this:

mysql> load data infile "myfile.txt" into my_table;

The file should be in the database folder, i.e. c:\MYSQL\DATA\my_database (if you're using Win). If you're using Linux, I have no idea what to do.

Just dump the file in the database folder along with all the other files and MySQL will pick it up automatically from the 'INFILE' specification, assuming you have already specified 'use my_database'.

Btw at the end of each line in a txt file (before the line return) it's a good idea to stick a tab in. This is stated in the MySQL manual and I've always done it.

Norman