Re: Multiple table insert using a CSV list as the data source

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(at)citlink(dot)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(at)khine(dot)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