0

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?

5

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.
  • Thanks @Mike T. In fact I did all the steps that you told, but that doesn't show me the data, but shows me only a row with all the column. The answer is: query result with 1 row discarded. Query returned successfully: 0 rows affected, 156 ms execution time. If you know the problem can you tell me, please? Thanks! – Po Po Mar 23 '15 at 11:24
  • please, could you tell me, how can I update geom column for the linestring? Thank you! – Po Po Mar 25 '15 at 13:47
  • It really depends on how the data for the line is organised. Also, that's a different question. – Mike T Mar 25 '15 at 18:39
6

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.

  • 2
    Here's a blog post where the author walks the reader through the exact steps for using ogr2ogr to import a CSV into PostgreSQL. IMO this is a recommended approach because ogr2ogr will automatically create the geometry column and a corresponding spatial index: geoexamples.blogspot.com/2011/07/… – elrobis Mar 19 '15 at 13:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.