How do I import data from a excel sheet onto a PGSQL database on linux?
Add a comment
|
5 Answers
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.
1 Comment
leonbloy
+1 Be prepared to deal with formats-datatypes issues, though.
If you do this on a regular basis, then you should look into Aqua Data Studio - I've found it pretty useful.
Comments
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.
Comments
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
Comments
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,";
}
