Go Back   HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials > Linux Forums > Programming/Scripts

Do you like HowtoForge? Please consider supporting us by becoming a subscriber.
Reply
 
Thread Tools Display Modes
  #1  
Old 4th November 2006, 12:30
edge edge is offline
Moderator
 
Join Date: Dec 2005
Location: The Netherlands
Posts: 2,034
Thanks: 265
Thanked 152 Times in 132 Posts
Question 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!
Reply With Quote
Sponsored Links
  #2  
Old 4th November 2006, 14:22
sjau sjau is offline
Local Meanie
 
Join Date: Apr 2006
Location: Switzerland
Posts: 1,146
Thanks: 4
Thanked 55 Times in 51 Posts
Default

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!";
Reply With Quote
The Following User Says Thank You to sjau For This Useful Post:
davince (19th December 2012)
  #3  
Old 4th November 2006, 14:34
edge edge is offline
Moderator
 
Join Date: Dec 2005
Location: The Netherlands
Posts: 2,034
Thanks: 265
Thanked 152 Times in 132 Posts
Default

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.
Reply With Quote
  #4  
Old 4th November 2006, 15:27
edge edge is offline
Moderator
 
Join Date: Dec 2005
Location: The Netherlands
Posts: 2,034
Thanks: 265
Thanked 152 Times in 132 Posts
Default

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;
Reply With Quote
  #5  
Old 4th November 2006, 15:33
sjau sjau is offline
Local Meanie
 
Join Date: Apr 2006
Location: Switzerland
Posts: 1,146
Thanks: 4
Thanked 55 Times in 51 Posts
Default

Well, you did not select the mysql database anywhere in there
Reply With Quote
  #6  
Old 4th November 2006, 15:57
edge edge is offline
Moderator
 
Join Date: Dec 2005
Location: The Netherlands
Posts: 2,034
Thanks: 265
Thanked 152 Times in 132 Posts
Default

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');
Reply With Quote
  #7  
Old 4th November 2006, 16:05
edge edge is offline
Moderator
 
Join Date: Dec 2005
Location: The Netherlands
Posts: 2,034
Thanks: 265
Thanked 152 Times in 132 Posts
Default

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;
}
Reply With Quote
  #8  
Old 27th November 2012, 18:24
JhayCamba JhayCamba is offline
Junior Member
 
Join Date: Nov 2012
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #9  
Old 27th November 2012, 21:35
sjau sjau is offline
Local Meanie
 
Join Date: Apr 2006
Location: Switzerland
Posts: 1,146
Thanks: 4
Thanked 55 Times in 51 Posts
 
Default

I don't understand what you try to do and I think it would be best to open a new thread for your problem.
__________________
"Common sense is not as common as commonly believed" by sjau

Auto-Install Script for ISPConfig and Horde on a Vanilla Debian Stable

Need more Repos for Ubuntu? Repository Generator
Need more Repos for Debian? Debian Repository Generator
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Systemimager (rsync) doesn't copy all comedit HOWTO-Related Questions 11 19th January 2007 17:17
HotSaNIC domino Tips/Tricks/Mods 23 6th November 2006 05:19
jamed up my table borders?? Boon-Dog-Danny Installation/Configuration 5 23rd September 2006 16:12
Chrooted SSH apps bkrausz Tips/Tricks/Mods 14 29th August 2006 23:32
site - databases trueshanti Installation/Configuration 20 26th May 2006 01:28


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


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