Transferring Data to Ubuntu PostgreSQL from Excel

Question

I am running ubuntu 12.04 LTS. I have data in an excel spreadsheet that I wish to import into Postgres, from my windows 7 machine to my ubuntu machine. Someone else, who is very fond of open source software, is consistently telling me about the virtues of open source software as opposed to proprietary windows-based software, and therefore took it upon himself to set me up a fully functioning ubuntu machine complete with Postgres. I am a windows person, or at least that is my primary operating system with regards to computers.

The data contains fields relating to Field ID, First Name, Last Name, Address, and Zip Code. The spreadsheet has approximately 1500 rows.

My questions, in order:
1) How do I determine if Postgres is properly installed and running on my ubuntu machine?

2) How do I create a database in Postgres which has those field names, and approximately 1500 rows?

3) How do I import that data from the .xls file format that it is in right now, into that database that I created, so that those 1500 rows become properly inserted into the Postgres field?

Being a windows user, my first instinct would be to work in a graphical environment, and use the copy/paste functions from one program to the other program. However, I do not think that Postgres is a graphical based program, so I do not think that the point/click method I have developed within my windows computer use will be very applicable.

4) I assume that within Postgres, there are functions which allow me (the user) to lookup specific fields on the basis of certain elements, and return a list of those elements. So what would the syntax be, lets say, if I wanted to look up all the rows with the last name "Obama"; would it return all rows which had the last name "Obama"? What would the return values look like, and what form would they be output in?

5) Could the output be fed to the windows environment, or does it have to stay within the ubuntu frame of reference?

-- Sorry if these questions show a complete lack of due diligence on my part with respect to open source software and ubuntu, but I am simply not familiar with the nuances of open source software, yet I am told that they are excellent resources to be proficient in, cost less, and are seemingly more capable, with respect to proprietary windows based software.

Answer 1

The data contains fields relating to Field ID, First Name, Last Name, Address, and Zip Code. The spreadsheet has approximately 1500 rows.

This is actually not a problem that is appropriate for a database management system (such as Postgres). If that is truly the extent of the data you have (1500 rows and 5 columns, no relations to other data tables), then this is a spreadsheet problem, and your friend, while meaning well, is WAY over-engineering your solution.

I would recommend loading that amount of data in Google Spreadsheets and then viewing it, editing it, etc on your new Ubuntu system using the web browser of your choice. It is quite straightforward.

Answer 2

1) installing on ubuntu http://wiki.postgresql.org/wiki/Detailed_installation_guides

2) first you need to log in to postgreSQL. log in : http://www.yolinux.com/TUTORIALS/LinuxTutorialPostgreSQL.html

EDIT: i'm sorry i was in a hurry to answer:

excel files can usually be imported in a sql environment by saving them to a CSV file format from excel

you don't need to create the rows in a sql database, they get created when you insert a new value into the table

Stackoverflow answer to this question : Import Excel Data into PostgreSQL 9.3

other answers: http://www.postgresql.org/message-id/CFEIKFONBJABHENFFFFOAEBDCDAA.Ben.Hooper@diskcopy.com.au

i'm a MSSQL/mysql guy, it's largely the same procedure

TIP: you need to see how to create databases, give right permissions to users, see how to create tables, and understand data types in an SQL environment.

ugh... plenty of tuts for that. you only need to search properly

3) yes, commands are given in the postgreSQL interactive command. see where command : http://www.postgresql.org/docs/8.2/static/sql-select.html please see WHERE clause from select

example : http://www.tutorialspoint.com/postgresql/postgresql_where_clause.htm

quote :

"SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;"

you select all fields from a table, and filter them given a value that you want to retrieve from a specific field. implying we have a table presidents, in a database USA:

in postgre interactive from ubuntu terminal, after loging in to postgreSQL #select the database psql usa

select ALL FIELDS (*) from table presidents, where field president_name is eq to "obama"

SELECT * From presidents WHERE president_name="Obama"

5) YES, output can be redirected from the server to the windows UI as long as you have a connection postgreSQL client, also your application can be fed data directly from the database. in this case, you have to allow the user that you want to use full privileges to connect outside of the postgrelSQL env (ubuntu).

I can only give you a personal opinion, in a production environment i would choose OSS, why? there are a lot of reasons, technical, scalability,flexibility,profesionalism(most of open source projects are picky about what goes into their app) etc, but i will be going for the emotional one: to reward that passion and itch that drives most of open source developers that keep this engine going. p.s.: don't feel intimidated, you just have to start, in a while the rest will seem easier. i do recommend for large data files NOT to use excel sheets but a proper database, even an sql lite. but that depends solely up to you and what you want to do with it.