Import/export tab delimited file

[fred999]

Hello php gurus!

i need help with a little script, i have to import a tab delimited files into a mysql db

file.tab

"user1"TAB"pass1"TAB"miscdata1_1"TAB"miscdata1_2"TAB"miscdata1_3"TAB"miscdata1_4"TAB
"user2"TAB"pass2"TAB"miscdata2_1"TAB"miscdata2_2"TAB"miscdata2_3"TAB"miscdata2_4"TAB

Here is what i have programmed with the help of google

<?
include('config.php');
$conn = mysql_connect($dbserver, $dbuser, $dbpass);
if (!$conn) {
		 echo "Impossible de se connecter la base de donns : " . mysql_error();
		 exit;
}
if (!mysql_select_db($dbname)) {
		 echo "Impossible d'accer la base de donns : " . mysql_error();
		 exit;
}
 
$fcontents = file('./file.tab'); 
for($i=0; $i<sizeof($fcontents); $i++) { 
	 $line = trim($fcontents[$i]); 
	 $arr = explode("\t", $line); 
	 #if your data is comma separated
	 # instead of tab separated, 
	 # change the '\t' above to ';' 
	 $sql = "insert into accounts values ('". implode("','", $arr) ."')"; 
	 mysql_query($sql);
	 echo $sql ."\n";
	 if(mysql_error()) {
		 echo mysql_error() ."\n";
	 } 
}
?>

This code will import the data in my database, something is annoying, all the db fields are filled with values with ""

This isnt a big problem, i can deal with it.

The second part of my script needs to be able to export the mysql database into a tab delimited file (same kind of file than file.tab) So i can add new entry in the database, export the content of the db to this file.

This is where i am jammed, i dont know how to do that, i dont know how to export the content of a db into a file to get the same result as the original file.tab

Any of the php masters here can help me? thanks in advance

fred

Sorry for the terrible english.

[fred999]

anyone?

[Roberdin]

It might be more helpful just go get phpMyAdmin. It's completely free, and offers all the facillities you desire (and lots more).

http://sourceforge.net/projects/phpmyadmin/

[Gnome_101]

Well, when you get the query, and the results, could you not then run through each row with a fetch row and then run it into the tab file. I not an expert, but try getting the data first, without the explode() function, then on each row run the explode, and dump it into a file.

[Troy]

First, you said you had the import working, but you ended up with double quotes in your values. You can strip those using code like:
[php]
$val = str_replace('"', '', $val);
[/php]

Here is example code that shows you how to connect to a mysql server, select a specific database, execute a SQL statement, then work with the data.
[php]
<?php

$server = "localhost"; // Name or IP of database server.
$user = ""; // username
$pwd = ""; // password
$db = ""; // Name of database to connect to.

if (!$cnn = mysql_connect($server,$user,$pwd )) {
die("mysql_connect() failed");
}

if (!mysql_select_db($db,$cnn)) {
die("Could not select database named ".$db);
}

/* Build your SQL statement however you need. */
$sql = "select * from mytable";

/* Execute the query. */
if (!$res = @mysql_query($sql)) {
die(mysql_error());
return false;
}
/* Create an array of arrays out of the recordset. */
while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) {
$data[] = $row;
}

/* Now iterate through the recordset creating a simple table. */
echo "";
echo "\n";
echo "";
echo "#";
foreach($data[0] as $key=>$val) {
echo "";
echo $key;
echo "";
}
echo "\n";
$row_cnt = 0;
foreach($data as $row) {
$row_cnt++;
echo "";
echo "".$row_cnt."";
foreach($row as $val) {
echo "";
echo $val;
echo "";
}
echo"\n";
}
echo "\n";

?>
[/php]

Next, you need to know how to save the recordset back into a TAB delimited file. This example uses the $data value from the code example above.
[php]
<?php
$fp = fopen("path_to_file_here", "w");

foreach($data as $row) {

$line = "";

foreach($row as $val) {
$line .= "\t\"".$val."\"";
}

/* Strip off the first TAB and add a carriage return. */
$line = substr($line, 1)."\n";

$fwrite($line);
}

fclose($fp);

?>
[/php]

I hope that helps! Enjoy the journey!