Importing text file into MySQL?

[edge]

I have a text file here, that I would like to import into a MySQL database (it's for an OScommerce webshop shop).

The text file looks like this:

Artikelcode|Leverancierscode|Omschrijving|Adviesprijs|Bruto prijs|Voorraad|Artikelgroep|Prijsgroep|Kortings percentage|Selectiecode|Selectiecode omschrijving|UNSPSC code|Standaard nalevertijd
AR8010BLK	|	AR8010BLK	|	RECESSED RAIL KIT FOR 600MM WIDE 19 BLACK	| 128,75	| 110,21	|	2	|	APC	|	030000	|		|	CR3	|	Cap.Rack 19 inch	|	43172605	|	10
AR8105BLK	|	AR8105BLK	|	APC NETSHELTER MONITOR SHELF			| 57,5		| 49,22		|	3	|	APC	|	030000	|		|	CR3	|	Cap.Rack 19 inch	|	24102001	|	15 
AR8122BLK	|	AR8122BLK	|	APC NETSHELTER HEAVY DUTY SHELF BLACK		| 97,5		| 83,46		|	1	|	APC	|	030000	|		|	CR3	|	Cap.Rack 19 inch	|	24102001	|	9

The web shop is using an other database, but as test I have created an empty MySQL named "testdb".
The testdb does not have any tables in it..

Now how can I import this text file into an empty MySQL database?

Any help appreciated!

[sjau]

Hiya Edge

First you need to create tables where the data actually should be inputed to

Then you can read data like this quite easily:

AR8010BLK	|	AR8010BLK	|	RECESSED RAIL KIT FOR 600MM WIDE 19 BLACK	| 128,75	| 110,21	|	2	|	APC	|	030000	|		|	CR3	|	Cap.Rack 19 inch	|	43172605	|	10
AR8105BLK	|	AR8105BLK	|	APC NETSHELTER MONITOR SHELF			| 57,5		| 49,22		|	3	|	APC	|	030000	|		|	CR3	|	Cap.Rack 19 inch	|	24102001	|	15 
AR8122BLK	|	AR8122BLK	|	APC NETSHELTER HEAVY DUTY SHELF BLACK		| 97,5		| 83,46		|	1	|	APC	|	030000	|		|	CR3	|	Cap.Rack 19 inch	|	24102001	|	9

(1) Read the text file into a variable

$file = "/path/to/text/file.txt";
$fp = fopen($file, "r");
$data = fread($fp, filesize($file));
fclose($fp);

(2) Then we can get rid of the tabs in there:

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

(3) Then we explode it at every line break

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

(4) Then we loop through all the array elements and explode them again and insert them into mysql

foreach($output as $var) {
$tmp = explode("|", $var);
$artikelcode = $tmp[0];
$leverancierscode = $tmp[1];
$omschrijving = $tmp[2];
....
....

$sql = "INSERT INTO table SET artikelcode='$artikelcode', leverancierscode='$leverancierscode', .....";
mysql_query($sql);
}
echo "Done!";

[edge]

Wow.. Thanks for that.

I was looking at the OScommerce database, and it looks like I need to do a lot to get this stuff imported to the correct tables.

It looks like the tables: categories, categories_description, products_description, products_to_categories are the needed tables.
(with a lot of rows in the tables :-/ )

I'm new to OScommerce, and as the OScommerce forum was down (it's working now again) I did not find an easy way to import a file like this..

Going over to the forum now to "maybe" find a tool to do this the easy way.

[edge]

Why does this not work?
No error show, but also no data added to the MySQL :/

The echo $Artikelgroep does show the Artikelgroep's.

Basicly (for now) I want to dump all the "Artikelgroep"'s in to the database named: "testdb", table: "categories_description", field: "categories_name"

<?php

// Set Mysql Variables
$host = 'localhost'; 
$user = 'root';
$pass = 'i-have-removed-it';
$db = 'testdb';
$table = 'categories_description'; 

$file = "/var/www/web1/web/handel/test/testdb.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("|", $var);
$Artikelcode = $tmp[0];
$Leverancierscode = $tmp[1];
$Omschrijving = $tmp[2];
$Adviesprijs = $tmp[3];
$Bruto_prijs = $tmp[4];
$Voorraad = $tmp[5];
$Artikelgroep = $tmp[6];
$Prijsgroep = $tmp[7];
$Kortings_percentage = $tmp[8];
$Selectiecode = $tmp[9];
$Selectiecode_omschrijving = $tmp[10];
$UNSPSC_code = $tmp[11];
$Standaard_nalevertijd = $tmp[12];

echo $Artikelgroep . "<br>";

$sql = "INSERT INTO $table SET categories_name='$Artikelgroep'";
mysql_query($sql);

}
echo "Done!";

?>

The MySQL looks like this:

CREATE TABLE `categories_description` (
  `categories_id` int(11) NOT NULL default '0',
  `language_id` int(11) NOT NULL default '1',
  `categories_name` varchar(32) NOT NULL default '',
  PRIMARY KEY  (`categories_id`,`language_id`),
  KEY `idx_categories_name` (`categories_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

[sjau]

Well, you did not select the mysql database anywhere in there

[edge]

I've done some recoding, and it's now working:
Now all I need to find (for now) is NOT to INSERT the record when "Artikelgroep" is the same as the one before.

The way it is now: "APC" is INSERT INTO 3 times.. Any suggestion (code snippet) for me :-)

<?php

// Set Mysql Variables

$username = "root"; 
$auth = 'i-have-removed-it';
$db = mysql_connect("localhost", $username, $auth); 
mysql_select_db("testdb",$db); 


$file = "/var/www/web1/web/handel/test/testdb.txt";
$fp = fopen($file, "r");
$data = fread($fp, filesize($file));
fclose($fp);

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

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

$language_id = "1";
$categories_id = 0;

foreach($output as $var) {
$categories_id = $categories_id + 1;

$tmp = explode("|", $var);

$Artikelgroep = $tmp[6];

echo " categories_id: " . $categories_id . " Artikelgroep: " . $Artikelgroep . "<br>";

mysql_query("INSERT INTO categories_description (categories_id,language_id,categories_name) VALUES('$categories_id','$language_id','$Artikelgroep')") or die("Insert failed: " . mysql_error()); 

}
echo "Done!";

?>

-- 
-- Dumping data for table `categories_description`
-- 

INSERT INTO `categories_description` VALUES (4, 1, '');
INSERT INTO `categories_description` VALUES (3, 1, ' APC');
INSERT INTO `categories_description` VALUES (2, 1, ' APC');
INSERT INTO `categories_description` VALUES (1, 1, ' APC');

[edge]

Okay.. Got the "same" problem fixed..
Please do not laugh of my code :-)

if ( $Artikelgroep_tmp == $Artikelgroep ) 
{
	echo "same";
}
else
{
	mysql_query("INSERT INTO categories_description (categories_id,language_id,categories_name) VALUES('$categories_id','$language_id','$Artikelgroep')") or die("Insert failed: " . mysql_error()); 
	$Artikelgroep_tmp = $Artikelgroep;
}

[JhayCamba]

Hi im a 17yrs old student and i learn a lot from this forum thank you so much,but i have a little problem here,i want my txtfile to upload on my page and link the txtfile content in the text-area line by line and import it on MySQL.
sorry for my poor English, please help thanks

[sjau]

I don't understand what you try to do and I think it would be best to open a new thread for your problem.