Skip site navigation (1) Skip section navigation (2)

Re: convert text file to database

From: Shane Ambler <pgsql(at)Sheeky(dot)Biz>
To: e-letter <inpost(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: convert text file to database
Date: 2008-04-13 11:20:36
Message-ID: 4801EC84.3040408@Sheeky.Biz (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
e-letter wrote:
> Readers,
> I play chess and occasionally (i.e. when I win!)  I save the moves to a
> text file, of the following format:
> [Event "Online Game"]
> [Site ""]
> [Date ""]
> [Round "1"]
> [White "player1"]
> [Black "player2"]
> [Result "1-0"]
> [Termination "time forfeit"]
> [UTCDate ""]
> [TimeControl "1200"]
> 1. c4 e5 2. d4 Nc6...8. Bg5
> 1-0
> and:
> [Event "Online Game"]
> [Site ""]
> [Date ""]
> [Round "1"]
> [White "player1"]
> [Black "player2"]
> [Result "1-0"]
> [Termination "normal"]
> [UTCDate ""]
> [TimeControl "1200"]
> 1. c4 e5 2. d4 f6 3. dxe5...25. Qd8# 1-0
> There are various other termination options such as 'abandoned',
> 'draw', etc. My text file, now numbering about 4000 lines consists of
> each game saved in this format, each game separated by a carriage
> return.
> Presumeably I would have to edit the text file to create fields
> separated via commas?

At 4000 lines I would probably come up with a small program (or perl 
script etc) that scans the file and inserts each piece of data into the 
db where it belongs. But you can manually turn this into a csv file to 
do the import. (a few find and replace's will make light work of it)

> I guess I could create a database 'chess games' with a table for each
> field: event; site; date; termination; etc., correct?

A database called chessmatches one table called chessgames with many 
columns. One column being one piece of information about the game.

Something like -

CREATE TABLE chessgames (
    id serial PRIMARY KEY,
    event text,
    site text,
    gamedate date,
    round integer,
    whitename text,
    blackname text,
    result text,
    termination text,
    utcdate date,
    timecontrol time

 From the above game data each line would go into the corresponding 
column as one row of data - being one game recorded.

gamedate could be a timestamp to also record the time of day the game 
was played. utcdate and timecontrol may be able to be joined in a 
similar way.

Most dba's would make termination an integer column linking to a table 
of termination choices. This could also be applied to event, site and 
player names.

> What about the moves? Should I create a table column called 'moves',
> in which case how to ensure data is placed in this database column? I
> would also like to perform searches such as: "find me all games played
> during the month of January"; or "show me the game with the fewest
> number of moves"; or "how many games have I won via time forfeit?".

This will depend mostly on what you want to do with this data. One 
option is to add another text column called moves to the above table. 
 From that you can view the list of moves when you wish.

Another option could be to have a second table to store the list of 
moves. This example will probably be overkill as I doubt you will be 
doing any kind of analysis where you want to get stats on how many games 
you won where the third last move was to e4.


Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

In response to

pgsql-novice by date

Next:From: Derrick BettsDate: 2008-04-13 18:38:58
Subject: Re: ORDER BY Clause
Previous:From: e-letterDate: 2008-04-13 06:58:31
Subject: convert text file to database

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group