Importing text file into MySQL?

Discussion in 'Programming/Scripts' started by kodabear, Jan 4, 2017.

  1. kodabear

    kodabear New Member

    I have a code that I took from https://www.howtoforge.com/community/threads/importing-text-file-into-mysql.7925/ and changed to what I needed it for but when I run the program it doesn't import the data into the database
    PHP:
    <?php

    // Set Mysql Variables

    $username ="root";
    $auth ='i-have-removed-it';
    $db mysql_connect("localhost"$username$auth);
    mysql_select_db("testdb",$db);


    $file ="/tmp/map_datafile_test.txtt";
    $fp fopen($file,"r");
    $data fread($fpfilesize($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);
    $GeoID $tmp[0];
    $X $tmp[1];
    $Y $tmp[2];
    $Wood $tmp[3];
    $Clay $tmp[4];
    $Iron $tmp[5];
    $Stone $tmp[6];
    $Food $tmp[7];
    $TerrainSpecificTypeID $tmp[8];
    $TerrainCombatTypeID $tmp[9];
    $RegionID $tmp[10];



    echo 
    " categories_id: "$categories_id ." Artikelgroep: "$Artikelgroep ."<br>";


    $sql ="INSERT INTO `World_Map`(`GeoID`, `X`, `Y`, `Wood`, `Clay`, `Iron`, `Stone`, `Food`, `TerrainSpecificTypeID`, `TerrainCombatTypeID`, `RegionID`) VALUES ('$GeoID','$X','$Y','$Wood','$Clay','$Iron','$Stone','$Food','$TerrainSpecificTypeID ','$TerrainCombatTypeID ','$RegionID')"ordie("Insert failed: "mysql_error());
    mysql_query($sql);}
    echo 
    "Done!";

    ?>
    needed it for but when I run the program it doesn't import the data into the database
    Code:
    GeoID|X|Y|Wood|Clay|Iron|Stone|Food|TerrainSpecificTypeID|TerrainCombatTypeID|RegionID
    7025277|279|-1321|0|0|0|0|0|62|14|31
    7025278|279|-1320|0|0|0|0|0|62|14|31
    7025279|279|-1319|0|0|0|0|0|62|14|31
    7025280|279|-1318|0|0|0|0|0|62|14|31
    7025281|279|-1317|0|0|0|0|0|62|14|31
    7025282|279|-1316|0|0|0|0|0|62|14|31
    7025283|279|-1315|0|0|0|0|0|62|14|31
    7025284|279|-1314|0|0|0|0|0|62|14|31
    7025285|279|-1313|0|0|0|0|0|62|14|31
    7025286|279|-1312|0|0|0|0|0|62|14|31
    and the code i am curently using is
     
    Last edited: Jan 4, 2017
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    The:

    mysql_query($sql);

    after the line

    $sql = "INSERT INTO `World_Map`(`Geo ........

    is missing.
     
  3. kodabear

    kodabear New Member

    fixed the and a few other issue and got it to work with the mini data map file but when i try the large one which is 305+mb it doesnt add anything to the database
     
  4. kodabear

    kodabear New Member

    around 8,600,000 lines in the full map file
     
  5. ---> It can be done with the help of LOAD DATA INFILE statement. To illustrate the concept we are having the following data, separated by tab, in ‘A.txt’ whose path is d:/A.txt:

    Code:
    100 John  USA 10000
    101 Paul  UK  12000
    102 Henry NZ  11000
    103 Rick  USA 17000
    104 Corey USA 15000
    --> We want to load the data of A.txt into the following table named employee_tbl:

    Code:
    mysql> Create table employee_tbl(Id Int, Name varchar(20), Country Varchar(20),Salary Int);
    
    --> Now, the transfer of data from a file to a database table can be done with the help of the following table:

    Code:
    mysql> LOAD DATA LOCAL INFILE 'd:\A.txt' INTO table employee_tbl;
    
    mysql> Select * from employee_tbl;
    --> Now, The Output Is Shoe below :-

    Code:
    +------+-------+---------+--------+
    | Id   | Name  | Country | Salary |
    +------+-------+---------+--------+
    | 100  | John  | USA     |  10000 |
    | 101  | Paul   | UK      |  12000 |
    | 102  | Henry | NZ      |  11000 |
    | 103  | Rick   | USA     |  17000 |
    | 104  | Corey | USA     |  15000 |
    +------+-------+---------+--------+
    
     

Share This Page