Importing text file into MySQL?

Discussion in 'Programming/Scripts' started by edge, Nov 4, 2006.

  1. edge

    edge HowtoForge Supporter

    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!
     
  2. sjau

    sjau Local Meanie

    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!";
    
     
  3. edge

    edge HowtoForge Supporter

    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.
     
  4. edge

    edge HowtoForge Supporter

    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;
    
     
  5. sjau

    sjau Local Meanie

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

    edge HowtoForge Supporter

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

    edge HowtoForge Supporter

    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;
    }
    
     
  8. JhayCamba

    JhayCamba New Member

    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
     
  9. sjau

    sjau Local Meanie

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

Share This Page