Thread: convert text file to database
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 "www.flyordie.com"] [Date "yyyy.mm.dd"] [Round "1"] [White "player1"] [Black "player2"] [Result "1-0"] [Termination "time forfeit"] [UTCDate "yyyy.mm.dd"] [TimeControl "1200"] 1. c4 e5 2. d4 Nc6...8. Bg5 1-0 and: [Event "Online Game"] [Site "www.flyordie.com"] [Date "yyyy.mm.dd"] [Round "1"] [White "player1"] [Black "player2"] [Result "1-0"] [Termination "normal"] [UTCDate "yyyy.mm.dd"] [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? I guess I could create a database 'chess games' with a table for each field: event; site; date; termination; etc., correct? 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?". Yours,
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 "www.flyordie.com"]
> [Date "yyyy.mm.dd"]
> [Round "1"]
> [White "player1"]
> [Black "player2"]
> [Result "1-0"]
> [Termination "time forfeit"]
> [UTCDate "yyyy.mm.dd"]
> [TimeControl "1200"]
>
> 1. c4 e5 2. d4 Nc6...8. Bg5
> 1-0
>
> and:
>
> [Event "Online Game"]
> [Site "www.flyordie.com"]
> [Date "yyyy.mm.dd"]
> [Round "1"]
> [White "player1"]
> [Black "player2"]
> [Result "1-0"]
> [Termination "normal"]
> [UTCDate "yyyy.mm.dd"]
> [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
Your method looks fine I think, but do without the numbers in the moves field. IE: c4,c5;b1c3,g7f6;... Then perhaps write some functions to deal with them on searches maybe turn them into: [Step],[Move1],[Move2] 1,c4,c5 2,b1c3,g7f6 ... And at the same time insert them into 2 caching tables to speed up future searches: [Game],[Step],[Move1],[Move2] 0001,1,c4,c5 0001,2,b1c3,g7f6 ... And, [Game] 0001 ... Nick -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of e-letter Sent: Sunday, April 13, 2008 2:59 PM To: pgsql-novice@postgresql.org Subject: [NOVICE] convert text file to database 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 "www.flyordie.com"] [Date "yyyy.mm.dd"] [Round "1"] [White "player1"] [Black "player2"] [Result "1-0"] [Termination "time forfeit"] [UTCDate "yyyy.mm.dd"] [TimeControl "1200"] 1. c4 e5 2. d4 Nc6...8. Bg5 1-0 and: [Event "Online Game"] [Site "www.flyordie.com"] [Date "yyyy.mm.dd"] [Round "1"] [White "player1"] [Black "player2"] [Result "1-0"] [Termination "normal"] [UTCDate "yyyy.mm.dd"] [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? I guess I could create a database 'chess games' with a table for each field: event; site; date; termination; etc., correct? 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?". Yours, -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice -- No virus found in this incoming message. Checked by AVG. Version: 7.5.519 / Virus Database: 269.22.13/1376 - Release Date: 4/13/2008 1:45 PM No virus found in this incoming message. Checked by AVG. Version: 7.5.519 / Virus Database: 269.22.13/1376 - Release Date: 4/13/2008 1:45 PM No virus found in this outgoing message. Checked by AVG. Version: 7.5.519 / Virus Database: 269.22.13/1376 - Release Date: 4/13/2008 1:45 PM