HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials

HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials (http://www.howtoforge.com/forums/index.php)
-   Programming/Scripts (http://www.howtoforge.com/forums/forumdisplay.php?f=7)
-   -   Importing text file into MySQL? (http://www.howtoforge.com/forums/showthread.php?t=7925)

edge 4th November 2006 13:30

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!

sjau 4th November 2006 15: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:
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!";


edge 4th November 2006 15: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 16: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"

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;


sjau 4th November 2006 16:33

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

edge 4th November 2006 16: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 :-)

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


edge 4th November 2006 17:05

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


JhayCamba 27th November 2012 19: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 22: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.


All times are GMT +2. The time now is 15:50.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.