An import tool for pgAdmin

With pgAdmin 1.14 and prior releases, there were only two ways to insert data in a table:

  • use the restore tool (which uses pg_restore) ;
  • use the query tool to execute INSERT queries (COPY queries are allowed if they use a file, but not stdin/stdout).

There was no way to ask it to import a CSV file for example. And a thread began on this matter a week ago. As working on the COPY API of libpq was quite motivating, I worked on this a few hours, and finished yesterday with this patch.

This patch adds a new window that allows a user to choose a file, and to select all options available in the COPY command (but only options available for import, not export).

This window is displayed when someone selects the "Import" item in the table's contextual menu, as shown in the following screenshot:

importmenu.png

The import window has 5 tabs:

  • "File options" tab makes possible to choose a file, sets its format (text, CSV, or binary), and optionaly its encoding.

import_win_1.png

  • "Columns" tab allows the user to specify which columns are contained in the file. Right now, they must be in the same order than the table (because of the UI), but they don't have to be all present in the file.

import_win_2.png

  • "Misc Options", "Quote Options", and "NULL options" tabs are not used usually, but it could be handy to set another delimiter than the usual comma, amongs other possibilities.

import_win_3.png import_win_4.png import_win_5.png

When the user clicks the "Import" button, pgAdmin launches a COPY query that matches the selected options, and sends the file by chunks of 8KB. The scrollbar shows the progress of the import. If the import ends with success, the "Import" button is renamed "Done", and a click on it closes the dialog.

That was a really interesting patch to write, for the UI, and the COPY API. And it is a quite nice feature for pgAdmin.

Commentaires

1. Le lundi, février 27 2012, 12:08 par Andreas

Nice work.

Would not "Delimiter" fit best on the same page as the qutoing option. These are usually best configured at the same time.

2. Le mardi, février 28 2012, 11:14 par Guillaume Lelarge

Yes, it would probably be better this way. Thanks.

3. Le lundi, mars 26 2012, 13:55 par Mário Costa

Congratulations for this useful and powerful tool! Wonderful work!
I'm novice in this matters, and I have one serious doubt: Is this patch for Windows, and if it is how do I apply it?

4. Le lundi, mars 26 2012, 15:23 par Guillaume Lelarge

It works on Windows. To use it, you need pgadmin source code, and compile it. Or you'll have to wait the 1.16 release.

Ajouter un commentaire

Les commentaires peuvent être formatés en utilisant une syntaxe wiki simplifiée.

Fil des commentaires de ce billet