Results 1 to 6 of 6
  1. #1
    P5C768 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2009
    Location
    Denver, CO
    Posts
    95

    Import A Single Column Text File as a Table with Multiple Rows and Columns

    Hi all, I have an issue that I'm stuck on:



    I have a .txt file approx. 675,000 but only about 9,000 unique records. I want to get a table of unique records. There are actually no duplicate records in the file, it's just the format of the data. For example, it comes like this:

    ID: 1
    Name: Mr. Smith
    Age: 50
    Email: email address
    Join Date: 1/1/11

    And so on for 75 fields per record (thus the 675k or so total rows in the .txt file) I'd like to convert it to the standard access table format so i can email everyone in the database.

    Anyone have any thoughts on extracting this data? I've managed to extract the data portion of the row using instr, and I was just going to append all the data together, but it needs a common field to link on.

    Any help is appreciated!

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Are you:
    1. Reading one line at a time
    2. Determining what value is on the line [Eg: ID:, Name:, Age: . . .]
    3. Storing the value in a variable
    4. . . . till you get to another ID line
    5. Write all the values into your Table [which should already be created.
    6. Loop and start over.

    I have done this quite a bit with text files.

    The key components of the program are:
    1. Create a Loop that will keep reading lines & getting data from them till you get to the next ID: [assuming ID is unique for each person].
    2. Create code that will write the record [all 75 fields - if you need them all] to your Table.
    3. Clear all variables & start populating them again [Loop] for the next ID.

  3. #3
    P5C768 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2009
    Location
    Denver, CO
    Posts
    95
    I actually have already written something similar in excel but excel can't handle that many records. The other problem is the data structure. The data fields don't come in a consistent order. For example, contact name might be the 5th field down for one record and the 11th for the next record.

    This would pose a problem for records where data is listed before a primary key.

  4. #4
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    You've got me there . .. I don't know how you would write a program [which basically anticipates & depends on predictability] to do this.

    But if there is a way for YOU to figure out which ID the data goes with - couldn't you include that in your logic?

    I mean - how DO you determine which ID a Name goes with in your data?
    If you CAN determine it - then you should [hypothetically] be able to tell your program how to tackle it - no?
    Last edited by Robeen; 12-01-2011 at 02:01 PM. Reason: typo

  5. #5
    P5C768 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2009
    Location
    Denver, CO
    Posts
    95
    I'm not sure how I would do that. There is no break between the records and the records aren't in a consistent order, so the only way I can determine where records break is by the ID, but then I don't know if the data for that record is above or below the ID.

  6. #6
    P5C768 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2009
    Location
    Denver, CO
    Posts
    95
    Robeen, the client just made me aware that there is a unique identifier towards the beginning of the record (not always in the same relative spot), but all the data I need come after the unique ID.

    The data is still not in order, so I couldn't just say "after each ID, copy the 5th line down." I would need to refernce the field name. So for example, I would need a for each statement to find the ID, then look at the contact name below that ID but before the next ID and write to the table. Is that what you have done before with text files?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Table rows to columns
    By Rhino373 in forum Programming
    Replies: 5
    Last Post: 12-22-2011, 01:44 PM
  2. Relate rows in a single table
    By HPG in forum Access
    Replies: 6
    Last Post: 11-07-2011, 10:10 AM
  3. Unmatched with multiple columns and rows
    By sampson20 in forum Programming
    Replies: 1
    Last Post: 04-18-2011, 10:48 AM
  4. Access to Excel transferring multiple rows to single row
    By peter_lawton in forum Import/Export Data
    Replies: 10
    Last Post: 09-23-2009, 10:16 AM
  5. Replies: 0
    Last Post: 04-29-2009, 04:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums