Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2009
    Posts
    35

    Unanswered: Read from improperly formatted text file (was "VBA error")

    Hi,

    Does anybody know how to append specific data from a txt file?? e.g. I have an input file with a list:

    player 1
    game 1
    game 2
    game 3
    game 4
    player 2
    game 1
    game 2
    game 3
    player 1
    game 1
    game 2
    game 3
    game 4
    so on.......

    I want the code to output "just" player 1 followed by the game(s) they have played in the following format (without player 2 & their games). i.e.

    player 1
    game 1
    game 2
    game 3
    game 4

    player 1
    game 1
    game 2
    game 3
    game 4

    I have an idea how to do this but the only problem i can think of is "how to copy over just the games for player 1 without bringing over the games for player 2?? I can't rename the games for player 2 as the file is too large to edit just this data.

    Can anybody shed any light on this problem?? thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Try this:
    Code:
    SELECT *
    FROM [Text;HDR=NO;DATABASE=D:\mydir\;].myfile.txt;
    If it works, include a where clause.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh hang on - no you can't do this without looping. You'll need to read this in to a recordset.

    You could have done with a file that was formatted better (e.g. XML, a "proper" table etc).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    May 2009
    Posts
    258
    There's another way to do it, use Excel:

    1. Create a new Excel spreadsheet, with columns as follows:
      Code:
      Data	Player	Game
    2. Paste your data into the Data column
    3. Enter the following as the formula for cell B2:
      Code:
      =IF(ISERROR(FIND("player",A2)),B1,A2)
    4. Double-click the little black box in the lower right corner to fill down the formula to the rest of the data
    5. Enter the following as the formula for cell C2:
      Code:
      =IF(ISERROR(FIND("game",A2)),"",A2)
    6. Double-click the little black box in the lower right corner to fill down the formula to the rest of the data
    7. Move back to cell A1 and click Data|Filter|AutoFilter
    8. Just filter the Player column on the player whose games you want to see


    If the number of players isn't very many, you can just filter as needed, otherwise you can use a macro to go through players (1 to whatever) and copy out the games.

    Regards,

    Ax
    Last edited by Ax238; 06-09-09 at 14:29.

  5. #5
    Join Date
    Feb 2009
    Posts
    35
    thanks for the feedback. Ax, I have managed to get this running and is very similar to what I need. Although I require to only pull back data for one player. The results brought back player 1, game1, game 2, game 3, game 4, player 2, game 5 (all unique values once.) Where as I need player 1 game 1, game 2 etc, skip player 2 (and their games), next occurence of player 1, game 1 etc.

    Pootle, can this coding for access SQL i.e. in a query?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Steven - Access can retrieve this data as if it were a table. The problem you have is that ALL the data is in a single column, and the order of the data in that column is significant. Really this should be two columns, and the order should not be significant. So although you can retrieve the data, you can't manipulate this as you require in Access SQL, and it would be pretty tricky in ANSI SQL. You will have to do this procedurally in VBA.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    May 2009
    Posts
    258
    It seems like you didn't apply the filter as outlined in the instructions:
    1. Move back to cell A1 and click Data|Filter|AutoFilter
    2. Just filter the Player column on the player whose games you want to see
    Here's the data that I get after applying the formulas:
    Code:
    Data	Player	Game
    player 1	player 1	
    game 1	player 1	game 1
    game 2	player 1	game 2
    game 3	player 1	game 3
    game 4	player 1	game 4
    player 2	player 2	
    game 1	player 2	game 1
    game 2	player 2	game 2
    game 3	player 2	game 3
    player 1	player 1	
    game 1	player 1	game 1
    game 2	player 1	game 2
    game 3	player 1	game 3
    game 4	player 1	game 4
    Here's the same data, filtered by the "Player" column on the "player 1" value:
    Code:
    Data	Player	Game
    player 1	player 1	
    game 1	player 1	game 1
    game 2	player 1	game 2
    game 3	player 1	game 3
    game 4	player 1	game 4
    player 1	player 1	
    game 1	player 1	game 1
    game 2	player 1	game 2
    game 3	player 1	game 3
    game 4	player 1	game 4
    Am I missing something? This seems to me that this is what you are asking for. Make sure you are using the AutoFilter, not the Advanced Filter.

    Ax

  8. #8
    Join Date
    Feb 2009
    Posts
    35
    Yes it was my formulas that were causing the problem. I have finally got this working thanks Ax.

    Pootle, I will now try the same query your way and report back asap.

    Thanks guys

  9. #9
    Join Date
    May 2009
    Posts
    258
    Good to hear. Keep in mind that once you have the data well-formatted in Excel, you can copy it into an Access table and query it from there if you need to do more processing on it. You'll want to add another field for determining maybe which "session" the player plays the game, for example:
    Code:
    Player	Session	Game
    player 1	1	game 1
    player 1	1	game 2
    player 1	1	game 3
    player 1	1	game 4
    player 1	2	game 1
    player 1	2	game 2
    player 1	2	game 3
    player 1	2	game 4
    Then your query would just be:
    Code:
    SELECT Player, Game FROM Stats
    WHERE Player = 'player 1'
    ORDER BY Player, Session, Game
    Regards,

    Ax

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •