Announcement

Collapse
No announcement yet.

How to import XLS data to database?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to import XLS data to database?

    Hi guys,

    please let me know how to import data from XLS to database? Does it any macro for this? Or should I code it by myself?
    I am using MySQL database.

    Thanks!

  • #2
    Save it as a 'csv' file and import the data using the LOAD DATA INFILE ... statement.

    https://dev.mysql.com/doc/refman/5.5/en/load-data.html

    jsb

    Comment


    • #3
      Mitja,

      for imports from .cvs * I use this php code. But it is in php. If you find a way to write this in the Scriptcase I also recommend the solution.
      And I'm taking you for a beer, because we just nearby. :-)

      --------------------------------------------------------------------------------------------------------------------------------------
      <?php

      $con=mysqli_connect("localhost","xxxxxxx","xxxxxxx ","xxxxxxx");
      // preveri povezavo z bazo
      if (mysqli_connect_errno()) {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
      }

      $handle = @fopen("register_za_test.csv", "r");
      fgetcsv($handle); //preskoci prvo vrstico
      $values='';

      while (!feof($handle)) // Loop til end of file.
      {
      $buffer = fgets($handle, 4096); // Read a line.
      $ID= 0;
      $priimek_in_ime = 0;
      $naslov = 0;
      $posta = 0;
      $spol = 0;
      $telefon = 0;
      $mobitel = 0;
      $datum_rojstva = 0;
      $tip = 0;
      $clan_je = 0;
      $izkaznica = 0;
      $prejem_glasila = 0;
      $cl_2012 = 0;
      $obcina = 0;
      $cl_2013 = 0;
      $cl_2014 = 0;
      $cl_2015 = 0;
      $cl_2016 = 0;
      $status = 0;

      list($ID, $priimek_in_ime, $naslov, $posta, $spol, $telefon, $mobitel, $datum_rojstva, $tip, $clan_je, $izkaznica, $prejem_glasila, $cl_2012, $obcina, $cl_2013, $cl_2014, $cl_2015, $cl_2016, $status)=explode(";",$buffer);//Separate string by the means of ;
      //values.=($a,$b,$c);// save values and use insert query at last or

      // operacija z bazo - preveri, ce clan ze obstaja
      $sql = "SELECT * FROM clani WHERE clani.ID = $ID";
      $result = $con->query($sql);

      if ($result->num_rows > 0) {
      while($row = $result->fetch_assoc()) {
      $sql = "UPDATE clani
      SET ID = '$ID',
      priimek_in_ime = '$priimek_in_ime',
      naslov = '$naslov',
      posta = '$posta',
      spol = '$spol',
      telefon = '$telefon',
      mobitel = '$mobitel',
      datum_rojstva = '$datum_rojstva',
      tip = '$tip',
      clan_je = '$clan_je',
      izkaznica = '$izkaznica',
      prejem_glasila = '$prejem_glasila',
      cl_2015 = '$cl_2012',
      obcina = '$obcina',
      cl_2013 = '$cl_2013',
      cl_2014 = '$cl_2014',
      cl_2015 = '$cl_2015',
      cl_2016 = '$cl_2016',
      status = '$status'
      WHERE Id = $ID";
      $con->query($sql);
      }
      } else {
      $result = mysqli_query($con,"insert into clani VALUES ($ID, '$priimek_in_ime', '$naslov', '$posta', '$spol', '$telefon', '$mobitel', '$datum_rojstva', '$tip', '$clan_je', '$izkaznica', '$prejem_glasila', '$cl_2012', '$obcina', '$cl_2013', '$cl_2014', '$cl_2015', '$cl_2016', '$status')");

      }

      }
      $con->close();
      ?>

      Comment

      Working...
      X