PDA

View Full Version : Importing text file into MySQL?


edge
4th November 2006, 12:30
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|Adviespr ijs|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
4th November 2006, 14:22
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
4th November 2006, 14:34
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
4th November 2006, 15:27
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
4th November 2006, 15:33
Well, you did not select the mysql database anywhere in there :)

edge
4th November 2006, 15:57
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','$Artikelgr oep')") 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
4th November 2006, 16:05
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','$Artikelgr oep')") or die("Insert failed: " . mysql_error());
$Artikelgroep_tmp = $Artikelgroep;
}

JhayCamba
27th November 2012, 18:24
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. :confused:
sorry for my poor English, please help thanks :D

sjau
27th November 2012, 21:35
I don't understand what you try to do and I think it would be best to open a new thread for your problem.