CODEWITHSUNDEEP
linuxexcelpostgresql
ha1ogen
ha1ogen

Reputation: 13455

Excel and pgSQL

How do I import data from a excel sheet onto a PGSQL database on linux?

Upvotes: 1

Views: 729

Answers (5)

Iwak Teri
Iwak Teri

Reputation: 11

try this code :

<?php

$db = pg_connect("host=localhost port=5432 dbname=your_db user=postgres password=your_paswrd");
//call excel reader
require "excel_reader.php";
//press button
if(isset($_POST['submit'])){
    $target = basename($_FILES['fopenl']['name']) ;
    move_uploaded_file($_FILES['fopenl']['tmp_name'], $target);   
    $data = new Spreadsheet_Excel_Reader($_FILES['fopenl']['name'],false);   
//count row of excel xls
    $baris = $data->rowcount($sheet_index=0);    
//empty database before
    $drop = isset( $_POST["drop"] ) ? $_POST["drop"] : 0 ;
    if($drop == 1){
//empty employee table
             $truncate ="TRUNCATE TABLE employee";
             pgsql_query($truncate);
    };    
//import data from xls
    for ($i=1; $i<=$baris; $i++)
    {
//read from xls
      $emp_id = $data->val($i, 1);
      $date = $data->val($i, 2);
      $id_mac = $data->val ($i, 3);
      $val_a = $data->val($i, 4);
      $val_b = $data->val($i, 5);
      $stat = $data->val($i, 6);
      $note = $data->val ($i, 7);
//setelah data dibaca, masukkan ke tabel dari_finger
      $query_insert = "INSERT into employee (emp_id,date,id_mac,val_a,val_b,stat,note)values('$emp_id','$date','$id_mac','$val_a','$val_b','$stat','$note')";
//eksekusi query insert
      $insert = pg_query($query_insert);
    }    
  if(!$insert){
//jika import gagal
          die(pgsql_error());
      }else{
//jika impor berhasil
          echo " Data imported,";
    }

Upvotes: 0

Will
Will

Reputation: 915

I do this all the time and use Navicat[1] (about $79). You'll need a windows box to run the client as the linux client can't import from Excel.

You can import almost any data format (Access, Excel, DBF, Lotus ...), define a mapping between the source and destination which can be saved.

BTW I don't work for Navicat! Just I've tried a few DBA clients and this was by far the best for data imports.

[1] http://www.navicat.com/en/products/navicat_pgsql/pgsql_overview.html

Upvotes: 0

ChristopheD
ChristopheD

Reputation: 116177

Another option (a bit of a workaround, but should work) is to import the Excel file into Access and then upping the resulting table into PostgreSQL over an ODBC connection.

Upvotes: 0

caf
caf

Reputation: 239051

If you do this on a regular basis, then you should look into Aqua Data Studio - I've found it pretty useful.

Upvotes: 0

Evan Carroll
Evan Carroll

Reputation: 1

The easy way is to export the data from Excel, to a CSV by using the SAVE AS functionality. Then use psql's native \COPY feature. You can find more information about it by using the command \? COPY in psql, or by visiting the docs on COPY.

Upvotes: 1

Related Questions