Importing text file into MySQL?

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:

Code:

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!

 

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:

Code:

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

Code:

$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:

Code:

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

(3) Then we explode it at every line break

Code:

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

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

Code:

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!";

 

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.

 

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"

Code:

<?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:

Code:

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;

 

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

 

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

Code:

<?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!";

?>

Code:

-- 
-- 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');

 

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

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;
}

 

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

 

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