Parse Tab Delimited txt file into MySql

[mbk]

I have a Tab delimited txt file that I need to import into a mysql database, however I am having an issue in that I dont think there is an end of line character within the file.

 

The code below is something i have picked up via google and made a couple of amends to, can someone please look at this and see if they can see anything obviously wrong with it?

 

The txt file itself is in the same directory at the mintue as the php file and when opened with either open office word or spreadsheet doesnt contain any | between fields, just tabs.

 

I would include a link to the txt file however it is 75MB.

 

Thanks

<?php

// Set Mysql Variables
//$host = 'localhost';
//$user = 'root';
//$pass = 'ASsC9XlI';
//$db = 'testdb';
$table = 'on_market_dump';

$username = "user";
$pass = 'mypass';
$db = mysql_connect("localhost", $username, $pass);
mysql_select_db("testdb",$db); 

//mysql_connect($host,$user,$pass) or die(mysql_error());
$empty_table = "TRUNCATE TABLE '$table'";
mysql_query($empty_table) or die (mysql_error());

$file = "on_market.export_urls_rich.txt";
$fp = fopen($file, "r");
$data = fread($fp, filesize($file));
fclose($fp);

//$output = str_replace("\t|\t", "|", $data);

$output = explode("\n", $output);

//mysql_connect($host,$user,$pass) or die(mysql_error());

foreach($output as $var) {
$tmp = explode("\t", $var);
$productid = $tmp[0];
$prodid = $tmp[1];
$quality = $tmp[2];
$url_spec = $tmp[3];
$supplier_id = $tmp[4];
$highres = $tmp[5];
$lowres = $tmp[6];
$thumbnail = $tmp[7];
$uncatid = $tmp[8];
$catid = $tmp[9];
$manu_pn = $tmp[10];
$ean_upcs = $tmp[11];
$modelname = $tmp[12];
$onmarket = $tmp[15];
$countries = $tmp[16];
$updated = $tmp[17];


$sql = "INSERT INTO $table SET productid='$productid', prodid='$prodid', quality='$quality'";
mysql_query($sql)or die (mysql_error());

}
echo "Done!";


?>

[Catfish]

The txt file itself is in the same directory at the mintue as the php file and when opened with either open office word or spreadsheet doesnt contain any | between fields, just tabs.

 

There is no | between fields, only tab character.

What character is between each record? (A record is a single set of fields)

Usually there would be a newline I assume?

[mbk]

Thanks for the reply.

 

When I open the file, there is just white space between each field and then the same again between each record.

 

If I open it in Open Office Spreadsheet and set it to tab delimited then it automatically formats the fields and records correctly which leads me to believe there is some sort of end of record.

 

I have pasted the first two records - the header and first proper record below.

 

product_id	prod_id	Quality	URL	supplier_id	High_res_img	Low_res_img	Thumbnail_img	UNCATID	Category_ID	m_prod_id	ean_upcs	model_name	original_supplier_id	product_view	on_market	country_market_set	Updated
10657	PA-T3+=	ICECAT	http://prf.icecat.biz/index.cgi?product_id=10657;mi=start;smi=product;	11	http://images.icecat.biz/img/norm/high/10657-3366.jpg	http://images.icecat.biz/img/norm/low/10657-3366.jpg	http://images.icecat.biz/thumbs/10657.jpg	43201409	182	PA-T3+=	746320213880;0746320213880	1-Port Adapter T3	11		1	NL;BE;FR;UK;DE;DK;PL;HU;FI;NO;SE;CH;IT;US;AU	20100215225607

[Catfish]

looks like a newline to me. try changing the explode character from \n to \r\n or \n\r (can't remember which way it goes)

 

it might be using Windows newline characters

[mbk]

Catfish - thanks for the response.  I have partially got it working now, although strangely needed to include the line

$output = str_replace("\t|\t", "|", $data);

  which was initially commented out.

 

I am now wondering if there is an easier way to simply import the whole file into mysql, but maintain the relevant fields.  Instead of creating an array and picking out certain points, simply just drop the whole file in.

 

Is there a simpler way?

 

Thanks for your help.

[jl5501]

You can only import a whole file to MySQL if the data fields in the database table exactly match the fields in the file.

[jl5501]

If you feel that your table and file do match, then you may find your answer here

 

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

[mbk]

thanks for the replies.

 

OK - I have continued with the original script and have successfully imported 120,000 of the 400,000 records 

 

Then I get this error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '4 INCH DIGI', quality='ICECAT'' at line 1

 

Full code and DB structure is below

<?php

// Set Mysql Variables
//$host = 'host';
//$user = 'user';
//$pass = 'pass';
//$db = 'testdb';
$table = 'on_market_dump';

$username = "user";
$pass = 'pass';
$db = mysql_connect("host", $username, $pass);
mysql_select_db("testdb",$db); 

//mysql_connect($host,$user,$pass) or die(mysql_error());
$empty_table = "TRUNCATE TABLE $table";
mysql_query($empty_table) or die (mysql_error());

$file = "on_market.export_urls_rich.txt";

$fp = fopen($file, "r");
$data = fread($fp, filesize($file));
fclose($fp);

$output = str_replace("\t|\t", "|", $data);

$output = explode("\n", $output);

foreach($output as $var) {
$tmp = explode("\t", $var);
$productid = $tmp[0];
$prodid = $tmp[1];
$quality = $tmp[2];
$url_spec = $tmp[3];
$supplier_id = $tmp[4];
$highres = $tmp[5];
$lowres = $tmp[6];
$thumbnail = $tmp[7];
$uncatid = $tmp[8];
$catid = $tmp[9];
$manu_pn = $tmp[10];
$ean_upcs = $tmp[11];
$modelname = $tmp[12];
$onmarket = $tmp[15];
$countries = $tmp[16];
$updated = $tmp[17];


$sql = "INSERT INTO $table SET productid='$productid', prodid='$prodid', quality='$quality'";
mysql_query($sql)or die (mysql_error());

}
echo "Done!";


//The MySQL looks like this:

//CREATE TABLE `testdb`.`on_market_dump` (
//`productid` VARCHAR( 255 ) NOT NULL ,
//`prodid` VARCHAR( 255 ) NOT NULL ,
//`quality` VARCHAR( 255 ) NOT NULL
//) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;

?>

[jl5501]

you probably have apostrophes or some quoting in the data, so that will need to be escaped

 

I suggest you use addslashes() when you copy from the array

 

like $url_spec = addslashes($tmp[3]);

 

for all of them, or certainly any that are not numbers, and that will probably fix it

[mbk]

Thanks for the reply, now have everything working fine.

 

Just as a note, I did try the load data method and this also worked fine, as well as being very fast.

 

Your help was much appreciated.