Thread: Populating a database
How can I populate a database with data I've got in a spreadsheet without having to format it with all the terrible format with commas, sql orders and so on?? Or, at least, which is the easiest way to do it? Thanks in advance for your answer. Josep Sànchez [papapep]
On Mon, Nov 04, 2002 at 14:20:49 +0100, papapep <papapep@gmx.net> wrote: > How can I populate a database with data I've got in a spreadsheet > without having to format it with all the terrible format with commas, > sql orders and so on?? > Or, at least, which is the easiest way to do it? Normally using \copy in psql will work fairly well with csv or tsv files. If you need to process the data before loading it into tables, you might consider using perl. It is fairly easy to massage dta and you can use COPY statements to do the actual loads.
First of all thanks for your ultrafast answer ;-D
Now, having a look at the copy order, it seems I've got some problems
with file permissions although I'm the root and have put all the files
and directories to 0777 (yes, I know I have to log in postgresql with
another user than root, I've done it already). Where is suposed to be
the file I'm going to insert in the databases's table? If I tell to the
copy command something like :
\copy tablename from '\path\to\file.csv' using delimiters ','
Should it work?
Then, if yes, I don't know what I'm doing wrong, because I've set this
file to 0777 and also changed the owner to postgres.
Thanks in advance.
Josep Sànchez
[papapep]
On Mon, Nov 04, 2002 at 16:05:02 +0100, papapep <papapep@gmx.net> wrote: > First of all thanks for your ultrafast answer ;-D > Now, having a look at the copy order, it seems I've got some problems > with file permissions although I'm the root and have put all the files > and directories to 0777 (yes, I know I have to log in postgresql with > another user than root, I've done it already). Where is suposed to be > the file I'm going to insert in the databases's table? If I tell to the > copy command something like : > > \copy tablename from '\path\to\file.csv' using delimiters ',' > > Should it work? Are you sure you want to use \s? The normal file separator on unix systems is /. You may also have a problem with access to one of the directories in the path.
papapep wrote: > How can I populate a database with data I've got in a spreadsheet > without having to format it with all the terrible format with commas, > sql orders and so on?? > Or, at least, which is the easiest way to do it? Probably the easiest way, if you're good with perl, is to save the spreadsheet in a suitably helpful ASCII format (CSV or what not), then write a custom perl script that uses the Pg module to import the data. Alternatively, you could use perl (or anything else) to change the ASCII format into a big COPY statement and use \i in psql to import it. Last, but not least, if you've got Windows, you could use ODBC with an ODBC->postgres driver, though the details escape me (and the perl option is probably easier). > > > Thanks in advance for your answer. > > Josep Sànchez > [papapep] > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
Josep, > How can I populate a database with data I've got in a spreadsheet > without having to format it with all the terrible format with commas, > sql orders and so on?? > Or, at least, which is the easiest way to do it? EMS Hitech recently released their conversion utilities to the PostgreSQL community: ================================================== http://www.ems-hitech.com/pgsqlutils/download.phtml. EMS PostgreSQL Export is a cross-platform (Windows and Linux) product to export your data quickly from PostgreSQL databases to any of 12 available formats, including MS Excel, MS Word, HTML, TXT, and more. PostgreSQL Export includes a wizard, which allows you to set export options for each table visually (destination filename, exported fields, data formats, and many more) and a command line utility to export data from tables and queries in one-touch. ==================================================== -- -Josh Berkus Aglio Database Solutions San Francisco
Folks, > EMS Hitech recently released their conversion utilities to the PostgreSQL > community: > > ================================================== > http://www.ems-hitech.com/pgsqlutils/download.phtml. Ooops, my mistake. These utilities are shareware, not open-source. Still, they're cheap and seem to be exactly what you need. -- -Josh Berkus Aglio Database Solutions San Francisco