Re: Fwd: PostgreSQL questions - copy / paste new data and updating records

Hi,

My name is Alex and I'm part of a small team working on
http://www.tennisround.com/ - a tennis directory and scheduling platform.

I'm looking for help with a couple of things - 1) importing data from Excel
into Postgresql on an ad-hoc basis - copy / paste style or through the
PgAdmin UI and 2) looking up specific records (CTRL+F style or search by
keyword).

Couldn't find anything in the support forums. Can you guys help?

Thanks,

--
Alex Marinov
Tennis Round | Marketing & Business Development
Phone: 415-341-2533
http://www.tennisround.com

Follow us on http://twitter.com/tennisround
Friend us on http://facebook.com/tennisround
Video: http://www.youtube.com/tennisround
Blog: http://blog.tennisround.com/

On Sun, Sep 25, 2011 at 8:27 PM, Alex Marinov <alex(at)tennisround(dot)com> wrote:
> Hi,
>
> My name is Alex and I'm part of a small team working on
> http://www.tennisround.com/ - a tennis directory and scheduling platform.
>
> I'm looking for help with a couple of things - 1) importing data from Excel
> into Postgresql on an ad-hoc basis - copy / paste style or through the
> PgAdmin UI and 2) looking up specific records (CTRL+F style or search by
> keyword).

The best way to import data from excel is going to be programatically.
What I typically do is save the excel sheet as a csv delimited text
file and import it to a staging table with postgres's built in 'copy'
(which has a csv switch). The copy command can be invoked from
pgadmin or through psql via a script. By staging table I mean a
specially designated table that contains all text fields for the
import data. From there, you can move the data to proper tables with
queries.

merlin

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Another possible option might be the PG ODBC driver. IIRC excel can use
and ODBC connector to a database, but I've never actually done it.
Merlin's suggestion is probably the easiest.

As far as "looking up records", are you asking how to query a table in
postgres?

- --

Jon T Erdman

Chief Information Officer voice: (312) 285-6735
Progressive Practice, Inc. jon(at)progressivepractice(dot)com
P.O. Box 17288 www.progressivepractice.com
Rochester, NY 14617

On 09/26/2011 08:32 AM, Merlin Moncure wrote:
> On Sun, Sep 25, 2011 at 8:27 PM, Alex Marinov <alex(at)tennisround(dot)com> wrote:
>> Hi,
>>
>> My name is Alex and I'm part of a small team working on
>> http://www.tennisround.com/ - a tennis directory and scheduling platform.
>>
>> I'm looking for help with a couple of things - 1) importing data from Excel
>> into Postgresql on an ad-hoc basis - copy / paste style or through the
>> PgAdmin UI and 2) looking up specific records (CTRL+F style or search by
>> keyword).
>
> The best way to import data from excel is going to be programatically.
> What I typically do is save the excel sheet as a csv delimited text
> file and import it to a staging table with postgres's built in 'copy'
> (which has a csv switch). The copy command can be invoked from
> pgadmin or through psql via a script. By staging table I mean a
> specially designated table that contains all text fields for the
> import data. From there, you can move the data to proper tables with
> queries.
>
> merlin
>
-----BEGIN PGP SIGNATURE-----
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk6A13MACgkQRAk1+p0GhSGk/wCfZTGvflcTCuQyBz4RMf4oEVAA
ju4AnjxKXlbSYE1QaTx+f9J0YUgiFmM1
=+Zqh
-----END PGP SIGNATURE-----

On Sep 26, 2011, at 12:50 PM, Jon Erdman <jon(at)progressivepractice(dot)com> wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> Another possible option might be the PG ODBC driver. IIRC excel can use
> and ODBC connector to a database, but I've never actually done it.
> Merlin's suggestion is probably the easiest.
>
> As far as "looking up records", are you asking how to query a table in
> postgres?
>
> - --
>
> Jon T Erdman
>
> Chief Information Officer voice: (312) 285-6735
> Progressive Practice, Inc. jon(at)progressivepractice(dot)com
> P.O. Box 17288 www.progressivepractice.com
> Rochester, NY 14617

We have about 20,000 records of tennis facilities. Sometimes people call in with updates - new phone, new address, more courts, etc. To my surprise I couldn't figure out a way to look up Heather Farm Park, Walnut Creek, CA when I opened the table of records in pgAdmin. So I don't know how to find that listing to change its phone.

Thanks,
Alex

>
> On 09/26/2011 08:32 AM, Merlin Moncure wrote:
>> On Sun, Sep 25, 2011 at 8:27 PM, Alex Marinov <alex(at)tennisround(dot)com> wrote:
>>> Hi,
>>>
>>> My name is Alex and I'm part of a small team working on
>>> http://www.tennisround.com/ - a tennis directory and scheduling platform.
>>>
>>> I'm looking for help with a couple of things - 1) importing data from Excel
>>> into Postgresql on an ad-hoc basis - copy / paste style or through the
>>> PgAdmin UI and 2) looking up specific records (CTRL+F style or search by
>>> keyword).
>>
>> The best way to import data from excel is going to be programatically.
>> What I typically do is save the excel sheet as a csv delimited text
>> file and import it to a staging table with postgres's built in 'copy'
>> (which has a csv switch). The copy command can be invoked from
>> pgadmin or through psql via a script. By staging table I mean a
>> specially designated table that contains all text fields for the
>> import data. From there, you can move the data to proper tables with
>> queries.
>>
>> merlin
>>
> -----BEGIN PGP SIGNATURE-----
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAk6A13MACgkQRAk1+p0GhSGk/wCfZTGvflcTCuQyBz4RMf4oEVAA
> ju4AnjxKXlbSYE1QaTx+f9J0YUgiFmM1
> =+Zqh
> -----END PGP SIGNATURE-----

A good GUI for accessing records this way would be phpPgAdmin:

On 9/26/11 3:03 PM, "Alex Marinov TR" <alex(at)tennisround(dot)com> wrote:

>
>
>On Sep 26, 2011, at 12:50 PM, Jon Erdman <jon(at)progressivepractice(dot)com>
>wrote:
>
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>>
>> Another possible option might be the PG ODBC driver. IIRC excel can use
>> and ODBC connector to a database, but I've never actually done it.
>> Merlin's suggestion is probably the easiest.
>>
>> As far as "looking up records", are you asking how to query a table in
>> postgres?
>>
>> - --
>>
>> Jon T Erdman
>>
>> Chief Information Officer voice: (312) 285-6735
>> Progressive Practice, Inc. jon(at)progressivepractice(dot)com
>> P.O. Box 17288 www.progressivepractice.com
>> Rochester, NY 14617
>
>We have about 20,000 records of tennis facilities. Sometimes people call
>in with updates - new phone, new address, more courts, etc. To my
>surprise I couldn't figure out a way to look up Heather Farm Park, Walnut
>Creek, CA when I opened the table of records in pgAdmin. So I don't know
>how to find that listing to change its phone.
>
>Thanks,
>Alex
>
>
>>
>> On 09/26/2011 08:32 AM, Merlin Moncure wrote:
>>> On Sun, Sep 25, 2011 at 8:27 PM, Alex Marinov <alex(at)tennisround(dot)com>
>>>wrote:
>>>> Hi,
>>>>
>>>> My name is Alex and I'm part of a small team working on
>>>> http://www.tennisround.com/ - a tennis directory and scheduling
>>>>platform.
>>>>
>>>> I'm looking for help with a couple of things - 1) importing data from
>>>>Excel
>>>> into Postgresql on an ad-hoc basis - copy / paste style or through the
>>>> PgAdmin UI and 2) looking up specific records (CTRL+F style or search
>>>>by
>>>> keyword).
>>>
>>> The best way to import data from excel is going to be programatically.
>>> What I typically do is save the excel sheet as a csv delimited text
>>> file and import it to a staging table with postgres's built in 'copy'
>>> (which has a csv switch). The copy command can be invoked from
>>> pgadmin or through psql via a script. By staging table I mean a
>>> specially designated table that contains all text fields for the
>>> import data. From there, you can move the data to proper tables with
>>> queries.
>>>
>>> merlin
>>>
>> -----BEGIN PGP SIGNATURE-----
>> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>>
>> iEYEARECAAYFAk6A13MACgkQRAk1+p0GhSGk/wCfZTGvflcTCuQyBz4RMf4oEVAA
>> ju4AnjxKXlbSYE1QaTx+f9J0YUgiFmM1
>> =+Zqh
>> -----END PGP SIGNATURE-----
>
>--
>Sent via austinpug mailing list (austinpug(at)postgresql(dot)org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/austinpug

Link fail:

http://phppgadmin.sourceforge.net/doku.php?id=screenshots

Neal Brown
Spiceworks Inc.

On 9/26/11 3:43 PM, "Neal Brown" <neal(at)spiceworks(dot)com> wrote:

>A good GUI for accessing records this way would be phpPgAdmin:
>
>
>On 9/26/11 3:03 PM, "Alex Marinov TR" <alex(at)tennisround(dot)com> wrote:
>
>>
>>
>>On Sep 26, 2011, at 12:50 PM, Jon Erdman <jon(at)progressivepractice(dot)com>
>>wrote:
>>
>>> -----BEGIN PGP SIGNED MESSAGE-----
>>> Hash: SHA1
>>>
>>>
>>> Another possible option might be the PG ODBC driver. IIRC excel can use
>>> and ODBC connector to a database, but I've never actually done it.
>>> Merlin's suggestion is probably the easiest.
>>>
>>> As far as "looking up records", are you asking how to query a table in
>>> postgres?
>>>
>>> - --
>>>
>>> Jon T Erdman
>>>
>>> Chief Information Officer voice: (312) 285-6735
>>> Progressive Practice, Inc. jon(at)progressivepractice(dot)com
>>> P.O. Box 17288 www.progressivepractice.com
>>> Rochester, NY 14617
>>
>>We have about 20,000 records of tennis facilities. Sometimes people call
>>in with updates - new phone, new address, more courts, etc. To my
>>surprise I couldn't figure out a way to look up Heather Farm Park, Walnut
>>Creek, CA when I opened the table of records in pgAdmin. So I don't know
>>how to find that listing to change its phone.
>>
>>Thanks,
>>Alex
>>
>>
>>>
>>> On 09/26/2011 08:32 AM, Merlin Moncure wrote:
>>>> On Sun, Sep 25, 2011 at 8:27 PM, Alex Marinov <alex(at)tennisround(dot)com>
>>>>wrote:
>>>>> Hi,
>>>>>
>>>>> My name is Alex and I'm part of a small team working on
>>>>> http://www.tennisround.com/ - a tennis directory and scheduling
>>>>>platform.
>>>>>
>>>>> I'm looking for help with a couple of things - 1) importing data from
>>>>>Excel
>>>>> into Postgresql on an ad-hoc basis - copy / paste style or through
>>>>>the
>>>>> PgAdmin UI and 2) looking up specific records (CTRL+F style or search
>>>>>by
>>>>> keyword).
>>>>
>>>> The best way to import data from excel is going to be programatically.
>>>> What I typically do is save the excel sheet as a csv delimited text
>>>> file and import it to a staging table with postgres's built in 'copy'
>>>> (which has a csv switch). The copy command can be invoked from
>>>> pgadmin or through psql via a script. By staging table I mean a
>>>> specially designated table that contains all text fields for the
>>>> import data. From there, you can move the data to proper tables with
>>>> queries.
>>>>
>>>> merlin
>>>>
>>> -----BEGIN PGP SIGNATURE-----
>>> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>>>
>>> iEYEARECAAYFAk6A13MACgkQRAk1+p0GhSGk/wCfZTGvflcTCuQyBz4RMf4oEVAA
>>> ju4AnjxKXlbSYE1QaTx+f9J0YUgiFmM1
>>> =+Zqh
>>> -----END PGP SIGNATURE-----
>>
>>--
>>Sent via austinpug mailing list (austinpug(at)postgresql(dot)org)
>>To make changes to your subscription:
>>http://www.postgresql.org/mailpref/austinpug
>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 09/26/2011 03:03 PM, Alex Marinov TR wrote:
>
> We have about 20,000 records of tennis facilities. Sometimes people
> call in with updates - new phone, new address, more courts, etc. To my
> surprise I couldn't figure out a way to look up Heather Farm Park,
> Walnut Creek, CA when I opened the table of records in pgAdmin. So I
> don't know how to find that listing to change its phone.
>
> Thanks, Alex

Ah, yes you would use SQL for that, using the query tool:

SELECT * FROM table_name WHERE field_name = 'text value';

or to do a partial string match:

SELECT * FROM my_table WHERE the_address ~ 'Heather';

There might be a way to filter the record view in pgadmin, but I'm not sure.

There's plenty of documentation on the SQL language (as well as lots of
books):

http://www.postgresql.org/docs/9.0/static/sql.html
- --
Jon T Erdman (aka StuckMojo)
PostgreSQL Zealot

-----BEGIN PGP SIGNATURE-----
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk6A9vEACgkQRAk1+p0GhSEifwCfWI8uR24Pfp/hSlfUJOw4zZXN
+z4AnRR1brfV+OvTZGe6GLpX/CnOTpb7
=wLZz
-----END PGP SIGNATURE-----