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

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

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

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 16: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 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.

All times are GMT +2. The time now is 05:30.