5 Replies - 9531 Views - Last Post: 23 November 2009 - 03:29 PM Rate Topic: -----

#1 m-e-g-a-z  Icon User is offline

  • Winning
  • member icon


Reputation: 497
  • View blog
  • Posts: 1,453
  • Joined: 19-October 09

copying data from txt file to Postgres Database

Posted 22 November 2009 - 06:52 AM

Ive got a database that i have created using Postgres and i basically want to copy the data into the tables. I cant use insert as there is alot of data.

Im just having problems how to copy it into the database..

The data looks like this



CLASS TABLE DATA:

CIS199,Intro to Java,Fri,10:00:00,MB300,4
CIS188,Information Syst,Thu,14:00:00,MB230,5
CIS177,Maths,Mon,15:00:00,MB100,6
CIS166,Intro to Computing,Tue,10:00:00,MB300,5
CIS288,Database Systems,Mon,14:00:00,MB142,1
CIS299,Java two,Mon,10:00:00,MB111,2
CIS298,C++ ,Fri,10:00:00,MB110,2
CIS297,Web Design,Mon,16:00:00,MB109,3
CIS277,Algorithmics,Thu,14:00:00,MB200,2
CIS399,Information Retrieval,Tue,11:00:00,MB100,8
CIS388,Data Mining,Thu,10:00:00,MB309,1
CIS320,Final year projects,Fri,12:00:00,WH300,8
CIS333,AI,Fri,14:00:00,MB309,5

STUDENT TABLE DATA:

101,Lorry Ross,CS,1,18
102,Lydia Ken,CIS,1,18
103,Bob Chung,CS,1,18
104,Sonia Morrison,CIS,1,18
105,Mark Poppy,CS,1,19
106,Megan Chi,IT,1,20
201,Mark Johnson,CS,2,19
202,Mary Simpson,CS,2,19
203,Nick Sark,CS,2,19
204,Michael Smith,CS,2,20
205,Michele Sabatier,CS,2,21
206,Nick Farmer,CS,2,19
207,Mick Morris,CS,2,19
208,Diana Moss,CS,2,20
209,Nick Moss,CS,2,21
210,Mark Thomson,CS,2,19
211,Mary Johnson,CIS,2,19
212,Mark Simpson,CIS,2,19
213,Diana Sark,CIS,2,19
214,Michael Robinson,CIS,2,20
215,Michele Farmer,CIS,2,21
216,Nick Lambert,CIS,2,19
217,John Morris,CIS,2,19
218,Diana McDonald,IT,2,20
219,Nick Smith,IT,2,21
220,Tim Thomson,CIS,2,19
321,Mark Simpson,CS,3,21
322,Mary Sark,CS,3,21
323,Nick Smith,CS,3,21
324,Michael Sabatier,CS,3,22
325,Michele Farmer,CS,3,21
326,Nick Morris,CS,3,21
327,Mick Morris,CS,3,25
328,Diana Moss,CS,3,21
329,Nick Thomson,CS,3,21
330,Mark Johnson,CS,3,21
331,Mary Simpson,CS,3,20
332,Mark Sark,CS,3,20
333,Diana Robinson,CS,3,20
334,Michael Farmer,CIS,3,20
335,Michele Lambert,CIS,3,23
336,Nick Morris,CIS,3,23
337,Jonathan McDonald,CIS,3,21
338,Diana Smith,IT,3,23
339,Nick Thomson,CIS,3,22
340,Tim Blacksmith,CIS,3,21

ENROLLED TABLE DATA:

211,CIS288
203,CIS298
214,CIS297
105,CIS177
215,CIS297
104,CIS188
210,CIS297
338,CIS320
102,CIS177
338,CIS399
204,CIS288
204,CIS277
102,CIS199
203,CIS297
105,CIS199
331,CIS320
202,CIS299
205,CIS299
210,CIS298
331,CIS399
321,CIS399
210,CIS288
210,CIS277
204,CIS297
321,CIS320
328,CIS388
327,CIS388
211,CIS297
333,CIS399
215,CIS288
104,CIS199
214,CIS288
335,CIS333
340,CIS399
333,CIS320
104,CIS177
216,CIS288
335,CIS388
204,CIS298
102,CIS188
202,CIS288
202,CIS277
205,CIS288
205,CIS277
339,CIS388
327,CIS333
217,CIS297
324,CIS320
334,CIS388
326,CIS399
328,CIS333
329,CIS333
207,CIS297
203,CIS288
203,CIS277
324,CIS399
330,CIS388
103,CIS166
105,CIS188
207,CIS299
326,CIS320
332,CIS388
327,CIS320
103,CIS177
340,CIS388
209,CIS299
337,CIS388
327,CIS399
209,CIS297
219,CIS288
333,CIS388
208,CIS298
220,CIS288
326,CIS333
328,CIS399
329,CIS399
321,CIS388
328,CIS320
329,CIS320
104,CIS166
206,CIS299
324,CIS333
201,CIS298
206,CIS297
209,CIS298
213,CIS288
324,CIS388
330,CIS399
334,CIS320
321,CIS333
332,CIS399
330,CIS320
334,CIS399
106,CIS188
326,CIS388
332,CIS320
333,CIS333
339,CIS399
201,CIS297
206,CIS298
335,CIS320
201,CIS299
337,CIS333
103,CIS199
208,CIS299
339,CIS320
335,CIS399
340,CIS333
208,CIS297
338,CIS388
220,CIS297
325,CIS320
101,CIS166
206,CIS288
206,CIS277
323,CIS333
322,CIS333
325,CIS399
336,CIS388
209,CIS288
209,CIS277
331,CIS333
106,CIS177
102,CIS166
106,CIS199
201,CIS288
201,CIS277
331,CIS388
208,CIS288
208,CIS277
336,CIS333
218,CIS288
213,CIS297
322,CIS388
323,CIS388
103,CIS188
105,CIS166
338,CIS333
336,CIS320
101,CIS199
336,CIS399
202,CIS298
205,CIS298
210,CIS299
325,CIS388
212,CIS297
322,CIS320
323,CIS320
106,CIS166
325,CIS333
203,CIS299
322,CIS399
323,CIS399
207,CIS288
207,CIS277
101,CIS177
216,CIS297
217,CIS288
202,CIS297
205,CIS297
332,CIS333
101,CIS188
330,CIS333
329,CIS388
334,CIS333
212,CIS288
204,CIS299
337,CIS399
340,CIS320
207,CIS298
339,CIS333
337,CIS320

TUTOR TABLE DATA:

1,Rob Hoffman
2,James Butler
3,Kathy Bond
4,Theodora Stewart
5,Mike Richie
6,John Kay
7,Mary Tregear
8,Mark Robinson






basically i need to copy these data into these 4 tables..iv tryed using psql but im still having problems..

Is This A Good Question/Topic? 0
  • +

Replies To: copying data from txt file to Postgres Database

#2 AdaHacker  Icon User is offline

  • Resident Curmudgeon

Reputation: 452
  • View blog
  • Posts: 811
  • Joined: 17-June 08

Re: copying data from txt file to Postgres Database

Posted 22 November 2009 - 09:59 AM

You want the COPY statement. You can use that to import your CSV files like this:
COPY tablename (col1, col2, col3) FROM 'path/to/datafile.txt' WITH DELIMITER ',';

Was This Post Helpful? 0
  • +
  • -

#3 m-e-g-a-z  Icon User is offline

  • Winning
  • member icon


Reputation: 497
  • View blog
  • Posts: 1,453
  • Joined: 19-October 09

Re: copying data from txt file to Postgres Database

Posted 22 November 2009 - 11:45 AM

would table name be ENROLLED TABLE DATA: or would it be the actual name of my table in postgres?

Im kinda confuzed..
Was This Post Helpful? 0
  • +
  • -

#4 AdaHacker  Icon User is offline

  • Resident Curmudgeon

Reputation: 452
  • View blog
  • Posts: 811
  • Joined: 17-June 08

Re: copying data from txt file to Postgres Database

Posted 22 November 2009 - 05:11 PM

Now I'm confused. Why would you think the table name would be the name from your data file? Is all of this stuff actually in one physical file? If so, you're going to have to either break each table out into a separate file or write a script to do the import.

The Postgres COPY statement assumes that the file it's working with contains data for a single table. The "tablename" in the example I posted is your Postgres table. The col1, col2, and col3 are the names of the columns in the table that correspond to the fields in your data file. They are necessary if the order of the fields in your data file does not match the order of the columns in the database. The delimiter simply specifies what character separates the fields in your file.
Was This Post Helpful? 0
  • +
  • -

#5 m-e-g-a-z  Icon User is offline

  • Winning
  • member icon


Reputation: 497
  • View blog
  • Posts: 1,453
  • Joined: 19-October 09

Re: copying data from txt file to Postgres Database

Posted 23 November 2009 - 02:29 PM

Ahh i get it now..i would have to update the table wouldnt i to copy the data into the tables?


UPDATE student
COPY student (sid, sname, programme) FROM 'c:\studentdata.txt' WITH DELIMITER ',';

iv tryed that but it dont work .
Was This Post Helpful? 0
  • +
  • -

#6 AdaHacker  Icon User is offline

  • Resident Curmudgeon

Reputation: 452
  • View blog
  • Posts: 811
  • Joined: 17-June 08

Re: copying data from txt file to Postgres Database

Posted 23 November 2009 - 03:29 PM

View Postm-e-g-a-z, on 23 Nov, 2009 - 03:29 PM, said:

Ahh i get it now..i would have to update the table wouldnt i to copy the data into the tables?

What? No. You don't need an UPDATE because it's not an update statement. It's just COPY, like I posted. Read the documentation for the syntax.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1