Re: convert text file to database - Mailing list pgsql-novice
From | Shane Ambler |
---|---|
Subject | Re: convert text file to database |
Date | |
Msg-id | 4801EC84.3040408@Sheeky.Biz Whole thread Raw |
In response to | convert text file to database (e-letter <inpost@gmail.com>) |
List | 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 "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
pgsql-novice by date: