How to populate my table with data in excel format?

Question

I have 160000 rows of data in Excel and I want to put them in my table in PostgreSQL. How can I import X,Y coordinates from my excel file to my table in postgresql?

Answer 1

Save your Excel as a comma-delimited file, then:

COPY your_table FROM '/path/to/csv/file/data.csv' WITH DELIMITER ',' CSV HEADER;

Another option would be ogr2ogr.

Edit: after some more searching, the answers to this question should help.

Answer 2

Here's what I do for Microsoft Excel files:

1. Preprocess the source Excel Worksheet to ensure that the headers are on the first row, and all data from row 2 and downwards look normal. This may need to be revised if the next step has difficulties
2. Start Microsoft Access and create a Blank database. Navigate to "External Data", and choose to Import Excel, and follow the steps from the Wizard. The result should import cleanly into a new table. If there are import errors, revise them and this may require cleaning up the source Excel file in step 1.
3. In Access, Right-click the table and choose Export > ODBC Database. I normally choose the "Machine Data Source" tab and choose a DSN that I have previously created for a PostgreSQL database, which only needs to be done once. Those steps follow a few sections down.

4. Once the table is in PostgreSQL, view it from either psql or pgAdmin III, and make a few modifications to spatially enable it for PostGIS:

   -- Make a primary key from Access' ID column
   ALTER TABLE "MyTable" ADD CONSTRAINT "MyTable_pkey" PRIMARY KEY("ID");
   -- Add and populate a geometry column from X, Y columns
   SELECT AddGeometryColumn('MyTable', 'geom', 4326, 'POINT', 2);
   UPDATE "MyTable" SET geom = ST_SetSRID(ST_MakePoint("X", "Y"), 4326);
   

---

This step needs to be done only once: Install an ODBC driver for PostgreSQL, such as psqlodbc. It can also be installed from Application Stack Builder, which comes with PostgreSQL installed on Windows. If your version of MS Access is 32-bit (many are, even on 64-bit Windows), then you must install the 32-bit version. And if you have 64-bit Access, then install a 64-bit driver.

---

For each database, create a DSN (Data Source Name). This can be done from step 3, from the "Machine Data Source" tab:

1. Click New, and choose (typically) a User Data Source (it doesn't matter)
2. From the list of ODBC drivers, choose "PostgreSQL Unicode" or similar. If nothing suitable is not found, then you need to revise the ODBC installation steps, and check any 32-bit/64-bit issues.
3. Click Finish to setup the DSN.
4. Set "Data Source" to something unique (this is the DSN shown in step 3 of the export); set "Database" to the database name; set "Server" to the hostname, such as 'localhost' if the database is on your computer, or '192.168.0.123' for another computer; set "User Name" to the username on the database, such as 'postgres', optionally set "Password", otherwise this is prompted. It's a good idea to Test, then Save if all good.