CSV import / export

Hi,

While looking at the PgAdmin ToDo list, I saw CSV import as one of the
things to do. I just wanted to ask a few things before starting work on it,
so as to ensure that it's envisioned in the way I plan to work on it.

- Apart from CSV import, are we planning to add an 'export' to CSV via
the Backup dialog box ? (We could add a 'CSV radio button' under 'Format'
and change the radio buttons below to things like 'Include headers, column
separators, etc...). But importantly, since its not mentioned in the ToDo,
is an export to CSV (of a single table) even on the cards ? Frankly I ask
this also because currently the only way to export a table in CSV is to do a
'SELECT * FROM table' in SQL query box and save to file, which is quite
lengthy a process.
- I read a bit about wxWidgets and didn't see any relevant libraries
there. Are there any 'obvious' libraries (in wxWidgets or otherwise) that I
should use to parse csv into an array ? For e.g. PHP has a pretty neat
function like fgetcsv ... that converts a csv file to an array of arrays.
Anything similar to that, or should I have to make that myself ?
- I have outlined a few details of Import to CSV, please feel free to
change (or completely rewrite :) the way it should work. I'll try and
attempt this if no one else is working on it right now.

Import to CSV:

1. 'Restore' option when one right-clicks on a table name, should allow
for importing via CSV. A radio button above the file selection defaults to a
'Backup' file, but has an extra added option of a 'CSV file' along with it.
2. (While importing from a CSV file) A set of preferences that could
allow a user to alter CSV import preferences. For e.g. column-separator,
line-separators, 'include headers' etc.. (Personally I'd prefer defaults to
these to be modifyable, but File-Options-Preferences is too generic for
these settings. Any better place for such options ?)
3. During import, if an error occurs, A dialogue box is thrown that
provides for three options. Either mute successive errors if any, keep
showing them, or altogether stop the import.

That's all that I could muster with.
Any feedback on the above would be appreciated.

Thanks
*Robins Tharakan
*

Hi, Tharakan

This item is already in my plan.
I am thinking what function should be supported and got some idea.

There are some commercial pruduct that coded in Java.
Through the JDBC technology, they can support almost all database.
We can reference their function to design our component.

Import/Export should has themselves dialog. In future, they can be
grouped into a sub-menu with Backup/Restore.

The CSV(Comma-Separated Values) has itself standard "RFC 4180".
Website: http://www.ietf.org/rfc/rfc4180.txt

I plan to support it and some expansion.
--column delimiter can be comma(std), colon, semicolon or TAB;
(Text parser should accept any char or string to be delimiter, ideally.
However, this will make the function too complicated. Juest be an idea
now.)
--text can be quoted in single or double(std) quotation;
--multi-format date, time, numberic
--user can adjust column order to correspond with CSV item.
--and so on

wxWidgets has some class that support charset convertion.

-----------------------------------------------
Quan Zongliang
quanzongliang(at)gmail(dot)com
CIT Japan: http://www.cit.co.jp
CIT China: http://www.citbj.com.cn

Robins Tharakan a écrit :
> [...]
> While looking at the PgAdmin ToDo list, I saw CSV import as one of the
> things to do. I just wanted to ask a few things before starting work on
> it, so as to ensure that it's envisioned in the way I plan to work on it.
>
> * Apart from CSV import, are we planning to add an 'export' to CSV
> via the Backup dialog box ?

I don't think this is quite the same thing. I would prefer to see a
specific Export menu item in the contextual menu. It would allow to
export datas from view.

> (We could add a 'CSV radio button'
> under 'Format' and change the radio buttons below to things like
> 'Include headers, column separators, etc...).

This seems good to me, but not in the Backup dialog.

> But importantly,
> since its not mentioned in the ToDo, is an export to CSV (of a
> single table) even on the cards ?

Yes, yes, yes :)

> Frankly I ask this also because
> currently the only way to export a table in CSV is to do a 'SELECT
> * FROM table' in SQL query box and save to file, which is quite
> lengthy a process.

You're right. And it would be great too to be able to export to other
formats than CSV.

> * I read a bit about wxWidgets and didn't see any relevant libraries
> there. Are there any 'obvious' libraries (in wxWidgets or
> otherwise) that I should use to parse csv into an array ? For e.g.
> PHP has a pretty neat function like fgetcsv ... that converts a
> csv file to an array of arrays. Anything similar to that, or
> should I have to make that myself ?

None that I know.

> * I have outlined a few details of Import to CSV, please feel free
> to change (or completely rewrite :) the way it should work. I'll
> try and attempt this if no one else is working on it right now.
>
>
> Import to CSV:
>
> 1. 'Restore' option when one right-clicks on a table name, should
> allow for importing via CSV. A radio button above the file
> selection defaults to a 'Backup' file, but has an extra added
> option of a 'CSV file' along with it.

As for export, I would prefer a specific Import menu item.

> 2. (While importing from a CSV file) A set of preferences that could
> allow a user to alter CSV import preferences. For e.g.
> column-separator, line-separators, 'include headers' etc..
> (Personally I'd prefer defaults to these to be modifyable, but
> File-Options-Preferences is too generic for these settings. Any
> better place for such options ?)

Preferences seems the better place to me.

> 3. During import, if an error occurs, A dialogue box is thrown that
> provides for three options. Either mute successive errors if any,
> keep showing them, or altogether stop the import.
>

I think you should have a look at pgloader
(http://pgfoundry.org/projects/pgloader/), at least to get some ideas of
the difficulties it will bring. This projet is written in Python and
tries to ease a restore from a CSV file to a PostgreSQL table.

Last but not least, I think you should work with Quan who also expresses
his wishes to work on this TODO item (see this thread:
http://archives.postgresql.org/pgadmin-hackers/2008-11/msg00099.php).

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

Quan Zongliang a écrit :
> [...]
> This item is already in my plan.
> I am thinking what function should be supported and got some idea.
>

You should work together.

> There are some commercial pruduct that coded in Java.
> Through the JDBC technology, they can support almost all database.
> We can reference their function to design our component.
>
> Import/Export should has themselves dialog. In future, they can be
> grouped into a sub-menu with Backup/Restore.
>
> The CSV(Comma-Separated Values) has itself standard "RFC 4180".
> Website: http://www.ietf.org/rfc/rfc4180.txt
>
> I plan to support it and some expansion.
> --column delimiter can be comma(std), colon, semicolon or TAB;
> (Text parser should accept any char or string to be delimiter, ideally.
> However, this will make the function too complicated. Juest be an idea
> now.)

length delimiter is also an option.

> --text can be quoted in single or double(std) quotation;
> --multi-format date, time, numberic
> --user can adjust column order to correspond with CSV item.

This would be great.

> --and so on
>
> wxWidgets has some class that support charset convertion.
>

Take a look at the pgloader tool in pgfoundry. It also deals with this
kind of problems. You should be able to get a complete list of issues
and ways to get over them.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

Oops. My bad. I should have checked the mailing list, before posting that
one then :)
Frankly, I think unless a job is high priority, its better if one person
works on this dedicatedly. Sometimes coordination/syncing takes more
resources than development. Especially when both are working 'in their spare
time'.

Not an issue at all. I just thought that I may have some spare time and
thought of this as something to work upon.

Guess I'll go back to the ToDo and pick something else to work on.
If I can do it, that is.

Regards,
Robins Tharakan

---------- Forwarded message ----------
From: Guillaume Lelarge <guillaume(at)lelarge(dot)info>
Date: 2008/11/27
Subject: Re: [pgadmin-hackers] CSV import / export
To: Quan Zongliang <quanzongliang(at)gmail(dot)com>
Cc: Robins Tharakan <tharakan(at)gmail(dot)com>, pgadmin-hackers(at)postgresql(dot)org

Quan Zongliang a écrit :
> [...]
> This item is already in my plan.
> I am thinking what function should be supported and got some idea.
>

You should work together.

> There are some commercial pruduct that coded in Java.
> Through the JDBC technology, they can support almost all database.
> We can reference their function to design our component.
>
> Import/Export should has themselves dialog. In future, they can be
> grouped into a sub-menu with Backup/Restore.
>
> The CSV(Comma-Separated Values) has itself standard "RFC 4180".
> Website: http://www.ietf.org/rfc/rfc4180.txt
>
> I plan to support it and some expansion.
> --column delimiter can be comma(std), colon, semicolon or TAB;
> (Text parser should accept any char or string to be delimiter,
ideally.
> However, this will make the function too complicated. Juest be an idea
> now.)

length delimiter is also an option.

> --text can be quoted in single or double(std) quotation;
> --multi-format date, time, numberic
> --user can adjust column order to correspond with CSV item.

This would be great.

> --and so on
>
> wxWidgets has some class that support charset convertion.
>

Take a look at the pgloader tool in pgfoundry. It also deals with this
kind of problems. You should be able to get a complete list of issues
and ways to get over them.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

> length delimiter is also an option.
Ok, will do

> Take a look at the pgloader tool in pgfoundry.
Found.

Thanks.

-----------------------------------------------
Quan Zongliang
quanzongliang(at)gmail(dot)com
CIT Japan: http://www.cit.co.jp
CIT China: http://www.citbj.com.cn