Thread: Multiple table insert using a CSV list as the data source
Hello,
I have a postgre database and I have a list of about 12,000 entries. My
problem in short is that I am trying to transfer an Access database to
Postgres and I am trying to match a very broken Access db schema an input
the data onto an sort of OK postgre schema - if this makes any sense;^)
Anyway, I am able to extract the data from the Access db into a sort of
managable file, but I am having difficulties in writing the sql insert
statement so that ALL parts of the dataabse are filled in at the same time.
Here is the csv table and its fields:
CREATE TABLE "csv" (
"PRODUCT_ID" character varying(30),
"TYPE" character varying(15),
"ARTIST" character varying(254),
"DESCRIPTION" character varying(254),
"CATEGORY" character varying(30),
"CATEGORY2" character varying(30),
"CATEGORY3" character varying(30),
"LABEL" character varying(50),
"CATEGORY4" character varying(30),
"IMAGE_PATH" character varying(1),
"PRICE" character varying(15),
"LONG_DESCRIPTION" character varying(254),
"SHIP_RATE" smallint,
"WRITE_UP" character varying(1),
"TRACK_NO" smallint,
"TRACK_NAME" character varying(254),
"AUDIO" character varying(1)
);
**The one thing to note here is that Category, Category_2, Category_3 are in
the following format
'CD', 'Big Band / Swing', 'Swing'
and not by category.id
Now here are the three tables that need to be updated from this one file:
CREATE SEQUENCE "product_id_seq" start 1 increment 1 maxvalue 2147483647
minvalue 1 cache 1 ;
CREATE TABLE "product" (
"id" integer DEFAULT nextval('"product_id_seq"'::text) NOT NULL,
"status" integer,
"manufacturer" integer,
"name" character varying(128),
"model" character varying(128),
"shortdesc" text,
"longdesc" text,
"smallimage" character varying(128),
"largeimage" character varying(128),
"xlargeimage" character varying(128),
"pdf" character varying(128),
"manual_url" character varying(128),
"keywords" text,
"weight" numeric(8,3),
"volumetric" numeric(8,3),
"supplier" integer,
"supplierpart" character varying(128),
"vatrate" integer
);
CREATE TABLE "price" (
"id" integer DEFAULT nextval('"price_id_seq"'::text) NOT NULL,
"product" integer,
"type" integer,
"minqty" integer,
"price" numeric(8,2)
);
CREATE TABLE "category_product" (
"category" integer,
"product" integer
);
CREATE SEQUENCE "category_id_seq" start 1 increment 1 maxvalue 2147483647
minvalue 1 cache 1 ;
CREATE TABLE "category" (
"id" integer DEFAULT nextval('"category_id_seq"'::text) NOT NULL,
"name" character varying(128),
"parent" integer,
"description" character varying(255),
"view" integer,
"keywords" text,
"featured_1" integer,
"featured_2" integer,
"visible" integer DEFAULT 1
);
INSERT INTO "category" VALUES (376,'',366,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (-1,'_Home',1,NULL,2,NULL,NULL,NULL,0);
INSERT INTO "category" VALUES (3,'Video',1,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (4,'Big Band /
Swing',2,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (2,'CDs',1,'',2,NULL,25,NULL,1);
INSERT INTO "category" VALUES (5,'Blues / Soul',2,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (6,'Chart',2,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (7,'Classical /
Opera',2,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (8,'Country',2,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (9,'Jazz',2,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (10,'Popular',2,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (11,'Rock N Roll /
Rock',2,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (12,'Shows / Films',2,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (13,'Various',2,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (14,'World',2,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (1,'All
Products',NULL,NULL,NULL,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (15,'Adult',3,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (16,'Cartoon',3,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (17,'Children',3,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (18,'Documentries',3,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (19,'Education',3,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (20,'Feature Films',3,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (21,'Music',3,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (23,'Sport',3,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (22,'Special
Interest',3,'',2,NULL,NULL,NULL,1);
CREATE UNIQUE INDEX "category_id_key" on "category" using btree ( "id"
"int4_ops" );
SELECT setval ('"category_id_seq"', 23, 't');
I hope this makes some sense.
Thanks
Norman
zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
zz/********/z/****\zzz|****\*\zz|*******|z
z/^^^^^^^^/z/******\zz|*^^^^|*|z|*|^^^^^|z norman khine
zzzzzz/**/z|**/^^\**|z|*|zzz|*|z|*|zzzzzzz mailto:norman@khine.net
zzzzz/**/zz|*|zzzz|*|z|****/*/zz|*****|zzz purley
z/******/zz|*|zzzz|*|z|*|^^zzzzz|*|^^^|zzz UK
zzZ/**/zzzz|**\^^/**|z|*|zzzzzzz|*|zzzzzzz
zz/******/zz\******/zz|*|zzzzzzz|*|*****|z
z/^^^^^^/zzzz\^^^^/zzz|^|zzzzzzz|^^^^^^^|z
zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
Thanks for your reply,
I think I may have been misunderstood, the table csv was imported via the
odbc on to the postgre db, what I wanted to do was to populate the tables of
the other three tables with the data from this csv table.
For example in MS SQL Server you have an option to import text, access files
and link the data for table_1 to table_2 that is within the MS SQL Server,
therefore you are able to transpose the data in any way fit, obviously the
field types need to match.
I think your idea of recreating the postgre schema in Access will work, and
then moving the data accross this way, but I was really looking for a more
elegant route, perhaps a python script;^)
Anyway thanks
-----Original Message-----
From: terry [mailto:tg5027@citlink.net]
Sent: 14 July 2002 17:09
To: Norman Khine
Subject: Re: [NOVICE] Multiple table insert using a CSV list as the data
source
hi,
you are inserting an unnecessary step here by creating the text
files in the first place. you can simply export the access
tables directly to the postgres database by right clicking and
say export, using the odbc data source. if you are changing the
table definitions you can create the new table in access, export
it empty, and do an access query to append the current access
table to the new one.
hth,
terry
>> Hello,
>> I have a postgre database and I have a list of about 12,000
>> entries. My problem in short is that I am trying to transfer
>> an Access database to Postgres and I am trying to match a very
>> broken Access db schema an input the data onto an sort of OK
>> postgre schema - if this makes any sense;^)
>>
>> Anyway, I am able to extract the data from the Access db into
>> a sort of managable file, but I am having difficulties in
>> writing the sql insert statement so that ALL parts of the
>> dataabse are filled in at the same time.
>>
>> Here is the csv table and its fields:
>>
>> CREATE TABLE "csv" (
>> "PRODUCT_ID" character varying(30),
>> "TYPE" character varying(15),
>> "ARTIST" character varying(254),
>> "DESCRIPTION" character varying(254),
>> "CATEGORY" character varying(30),
>> "CATEGORY2" character varying(30),
>> "CATEGORY3" character varying(30),
>> "LABEL" character varying(50),
>> "CATEGORY4" character varying(30),
>> "IMAGE_PATH" character varying(1),
>> "PRICE" character varying(15),
>> "LONG_DESCRIPTION" character varying(254),
>> "SHIP_RATE" smallint,
>> "WRITE_UP" character varying(1),
>> "TRACK_NO" smallint,
>> "TRACK_NAME" character varying(254),
>> "AUDIO" character varying(1)
>> );
>>
>>
>> **The one thing to note here is that Category, Category_2,
>> Category_3 are in the following format
>>
>> 'CD', 'Big Band / Swing', 'Swing'
>>
>> and not by category.id
>>
>> Now here are the three tables that need to be updated from
>> this one file:
>>
>> CREATE SEQUENCE "product_id_seq" start 1 increment 1 maxvalue
>> 2147483647 minvalue 1 cache 1 ;
>>
>> CREATE TABLE "product" (
>> "id" integer DEFAULT nextval('"product_id_seq"'::text) NOT
>> NULL, "status" integer,
>> "manufacturer" integer,
>> "name" character varying(128),
>> "model" character varying(128),
>> "shortdesc" text,
>> "longdesc" text,
>> "smallimage" character varying(128),
>> "largeimage" character varying(128),
>> "xlargeimage" character varying(128),
>> "pdf" character varying(128),
>> "manual_url" character varying(128),
>> "keywords" text,
>> "weight" numeric(8,3),
>> "volumetric" numeric(8,3),
>> "supplier" integer,
>> "supplierpart" character varying(128),
>> "vatrate" integer
>> );
>>
>> CREATE TABLE "price" (
>> "id" integer DEFAULT nextval('"price_id_seq"'::text) NOT
>> NULL, "product" integer,
>> "type" integer,
>> "minqty" integer,
>> "price" numeric(8,2)
>> );
>>
>>
>> CREATE TABLE "category_product" (
>> "category" integer,
>> "product" integer
>> );
>>
>>
>>
>> CREATE SEQUENCE "category_id_seq" start 1 increment 1
>> maxvalue 2147483647 minvalue 1 cache 1 ;
>>
>>
>> CREATE TABLE "category" (
>> "id" integer DEFAULT nextval('"category_id_seq"'::text) NOT
>> NULL, "name" character varying(128),
>> "parent" integer,
>> "description" character varying(255),
>> "view" integer,
>> "keywords" text,
>> "featured_1" integer,
>> "featured_2" integer,
>> "visible" integer DEFAULT 1
>> );
>>
>> INSERT INTO "category" VALUES
>> (376,'',366,'',2,NULL,NULL,NULL,1); INSERT INTO "category"
>> VALUES (-1,'_Home',1,NULL,2,NULL,NULL,NULL,0); INSERT INTO
>> "category" VALUES (3,'Video',1,'',2,NULL,NULL,NULL,1); INSERT
>> INTO "category" VALUES (4,'Big Band /
>> Swing',2,'',2,NULL,NULL,NULL,1);
>> INSERT INTO "category" VALUES
>> (2,'CDs',1,'',2,NULL,25,NULL,1); INSERT INTO "category" VALUES
>> (5,'Blues / Soul',2,'',2,NULL,NULL,NULL,1); INSERT INTO
>> "category" VALUES (6,'Chart',2,'',2,NULL,NULL,NULL,1); INSERT
>> INTO "category" VALUES (7,'Classical /
>> Opera',2,'',2,NULL,NULL,NULL,1);
>> INSERT INTO "category" VALUES
>> (8,'Country',2,'',2,NULL,NULL,NULL,1); INSERT INTO "category"
>> VALUES (9,'Jazz',2,'',2,NULL,NULL,NULL,1); INSERT INTO
>> "category" VALUES (10,'Popular',2,'',2,NULL,NULL,NULL,1);
>> INSERT INTO "category" VALUES (11,'Rock N Roll /
>> Rock',2,'',2,NULL,NULL,NULL,1);
>> INSERT INTO "category" VALUES (12,'Shows /
>> Films',2,'',2,NULL,NULL,NULL,1); INSERT INTO "category" VALUES
>> (13,'Various',2,'',2,NULL,NULL,NULL,1); INSERT INTO "category"
>> VALUES (14,'World',2,'',2,NULL,NULL,NULL,1); INSERT INTO
>> "category" VALUES (1,'All
>> Products',NULL,NULL,NULL,NULL,NULL,NULL,1);
>> INSERT INTO "category" VALUES
>> (15,'Adult',3,'',2,NULL,NULL,NULL,1); INSERT INTO "category"
>> VALUES (16,'Cartoon',3,'',2,NULL,NULL,NULL,1); INSERT INTO
>> "category" VALUES (17,'Children',3,'',2,NULL,NULL,NULL,1);
>> INSERT INTO "category" VALUES
>> (18,'Documentries',3,'',2,NULL,NULL,NULL,1); INSERT INTO
>> "category" VALUES (19,'Education',3,'',2,NULL,NULL,NULL,1);
>> INSERT INTO "category" VALUES (20,'Feature
>> Films',3,'',2,NULL,NULL,NULL,1); INSERT INTO "category" VALUES
>> (21,'Music',3,'',2,NULL,NULL,NULL,1); INSERT INTO "category"
>> VALUES (23,'Sport',3,'',2,NULL,NULL,NULL,1); INSERT INTO
>> "category" VALUES (22,'Special
>> Interest',3,'',2,NULL,NULL,NULL,1);
>>
>>
>> CREATE UNIQUE INDEX "category_id_key" on "category" using
>> btree ( "id" "int4_ops" );
>>
>> SELECT setval ('"category_id_seq"', 23, 't');
>>
>>
>> I hope this makes some sense.
>>
>> Thanks
>>
>> Norman
>>
>>
>> zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
>> zz/********/z/****\zzz|****\*\zz|*******|z
>> z/^^^^^^^^/z/******\zz|*^^^^|*|z|*|^^^^^|z norman khine
>> zzzzzz/**/z|**/^^\**|z|*|zzz|*|z|*|zzzzzzz
>> mailto:norman@khine.net
>> zzzzz/**/zz|*|zzzz|*|z|****/*/zz|*****|zzz purley
>> z/******/zz|*|zzzz|*|z|*|^^zzzzz|*|^^^|zzz UK
>> zzZ/**/zzzz|**\^^/**|z|*|zzzzzzz|*|zzzzzzz
>> zz/******/zz\******/zz|*|zzzzzzz|*|*****|z
>> z/^^^^^^/zzzz\^^^^/zzz|^|zzzzzzz|^^^^^^^|z
>> zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
>>
>>
>>
>> ---------------------------(end of
>> broadcast)--------------------------- TIP 5: Have you checked
>> our extensive FAQ?
>>
>> http://www.postgresql.org/users-lounge/docs/faq.html
--
terry
On Mon, 2002-07-15 at 00:29, Norman Khine wrote:
> Hello,
> I have a postgre database and I have a list of about 12,000 entries. My
> problem in short is that I am trying to transfer an Access database to
> Postgres and I am trying to match a very broken Access db schema an input
> the data onto an sort of OK postgre schema - if this makes any sense;^)
>
> Anyway, I am able to extract the data from the Access db into a sort of
> managable file, but I am having difficulties in writing the sql insert
> statement so that ALL parts of the dataabse are filled in at the same time.
What I do in this sort of case is to import the data straightforwardly
from the Access tables into temporary PostgreSQL tables, and then I
write a file of SQL commands which convert those tables into my final
tables. This doesn't have to be a single "INSERT ..." statement, and
indeed it usually isn't. Sometimes I might enclose it in a transaction
block if I want it to happen atomically.
I run the script of SQL commands using "psql -f filename.sql
<database>".
Once I am happy with the way my conversion script works, and the
PostgreSQL database is 'live', I drop the temporary tables with the old
Access data.
Regards,
Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Are you enrolled at http://schoolreunions.co.nz/ yet?
Hi Andrew,
Thanks for the advice, much appreciated.
I did put the Access table on the Posgre db, it is the csv table, of my
original post.
Can you advise on how to write an sql statement which will convert this one
table into my INSERT statement?
Is there such an SQL statement where you say:
Take table csv and put column 1, 2, 3 into table products where column 1
should map to products.column1 etc etc ...
or take table csv and put column1 and column 4 into table price
Can you provide me with an example, if possible?
Regards
Norman
-----Original Message-----
From: Andrew McMillan [mailto:andrew@catalyst.net.nz]
Sent: 14 July 2002 21:35
To: Norman Khine
Cc: Pgsql-Novice
Subject: Re: [NOVICE] Multiple table insert using a CSV list as the
datasource
On Mon, 2002-07-15 at 00:29, Norman Khine wrote:
> Hello,
> I have a postgre database and I have a list of about 12,000 entries. My
> problem in short is that I am trying to transfer an Access database to
> Postgres and I am trying to match a very broken Access db schema an input
> the data onto an sort of OK postgre schema - if this makes any sense;^)
>
> Anyway, I am able to extract the data from the Access db into a sort of
> managable file, but I am having difficulties in writing the sql insert
> statement so that ALL parts of the dataabse are filled in at the same
time.
What I do in this sort of case is to import the data straightforwardly
from the Access tables into temporary PostgreSQL tables, and then I
write a file of SQL commands which convert those tables into my final
tables. This doesn't have to be a single "INSERT ..." statement, and
indeed it usually isn't. Sometimes I might enclose it in a transaction
block if I want it to happen atomically.
I run the script of SQL commands using "psql -f filename.sql
<database>".
Once I am happy with the way my conversion script works, and the
PostgreSQL database is 'live', I drop the temporary tables with the old
Access data.
Regards,
Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Are you enrolled at http://schoolreunions.co.nz/ yet?
On Sun, Jul 14, 2002 at 22:10:36 +0100, Norman Khine <norman@khine.net> wrote: > > Is there such an SQL statement where you say: > > Take table csv and put column 1, 2, 3 into table products where column 1 > should map to products.column1 etc etc ... insert into products (column1, column2, column3) select * from csv; > or take table csv and put column1 and column 4 into table price insert into price select column1, column4 from csv;
On Sunday, July 14, 2002, at 02:10 , Norman Khine wrote: > Is there such an SQL statement where you say: > > Take table csv and put column 1, 2, 3 into table products where column 1 > should map to products.column1 etc etc ... > or take table csv and put column1 and column 4 into table price There might be a way to do all that in SQL, but I would do it with a short Perl script using the DBI module. It's fairly straightforward, and the DBI module's documentation is enough to get you going. If you don't like Perl, I'm sure Python, Tcl and Ruby have similar functionality. -- Chris music is what numbers feel like San Francisco, CA
On Sun, Jul 14, 2002 at 14:21:10 -0700, Chris Palmer <chris@nodewarrior.org> wrote: > On Sunday, July 14, 2002, at 02:10 , Norman Khine wrote: > > >Is there such an SQL statement where you say: > > > >Take table csv and put column 1, 2, 3 into table products where column 1 > >should map to products.column1 etc etc ... > >or take table csv and put column1 and column 4 into table price > > There might be a way to do all that in SQL, but I would do it with a > short Perl script using the DBI module. It's fairly straightforward, and > the DBI module's documentation is enough to get you going. If you are talking about after the data has been put into a temporary table, I don't think this would be a good idea. Doing the copy with one insert statement is going to be faster.
Thank you all, you have been very helpfull.
-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Bruno Wolff III
Sent: 14 July 2002 22:35
To: Chris Palmer
Cc: Pgsql-Novice
Subject: Re: [NOVICE] Multiple table insert using a CSV list as the
datasource
On Sun, Jul 14, 2002 at 14:21:10 -0700,
Chris Palmer <chris@nodewarrior.org> wrote:
> On Sunday, July 14, 2002, at 02:10 , Norman Khine wrote:
>
> >Is there such an SQL statement where you say:
> >
> >Take table csv and put column 1, 2, 3 into table products where column 1
> >should map to products.column1 etc etc ...
> >or take table csv and put column1 and column 4 into table price
>
> There might be a way to do all that in SQL, but I would do it with a
> short Perl script using the DBI module. It's fairly straightforward, and
> the DBI module's documentation is enough to get you going.
If you are talking about after the data has been put into a temporary
table, I don't think this would be a good idea. Doing the copy with
one insert statement is going to be faster.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)