Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2003
    Posts
    225

    Unanswered: Data import problems from text file

    I am trying to import data into my database from a text file,however i am unable get it to sort the columns into the correct way.

    7/31/2007 9:42:53 AM Application started.
    7/31/2007 9:44:07 AM Quit application
    7/31/2007 9:46:05 AM Application started.
    7/31/2007 9:47:50 AM Boolean CheckMachineState() : Object reference not set to an instance of an object.
    7/31/2007 9:47:50 AM The application Timer Handler has aborted, probably no connections with the PLC.
    7/31/2007 9:48:44 AM Quit application

    is how my data is stored in the text file

    I have trie importing the file and using the delimited option, yet when i choose "Space" as the seperator i end up with a new column for every word,whereas i really want data into just 4 columns..

    Date
    Time
    Am/Pm
    Problem

    What is the best way of doing this please.

    I have attached the txt file for you to see exactly what the data is like.

    Andy
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    In you MDB do next:
    Click on File, GetExternalData, Link tables.
    In Link form select "Look in:" (directory where the txt file is).
    In "Files of type" select TEXT FILES.
    Select "Trace00" (or write it in "File name" field), click on LINK button.
    "Link text wizard" form is going to open,
    follow wizard, when you get a Link on txt file,
    create Table1, (the same as txt file is).
    Create an APPEND query, (input = link on txt file, output = Table1).
    I think it can help you.

  3. #3
    Join Date
    Mar 2003
    Posts
    225
    ok cheers, i am a little lost at this point...

    "Link text wizard" form is going to open,
    follow wizard, when you get a Link on txt file,
    create Table1, (the same as txt file is).
    Create an APPEND query, (input = link on txt file, output = Table1).
    I think it can help you.

    I have the wizard open, however i am unsure with to choose, should i go for :

    delimited or fixed width?

    if i use the delimited should i use the space as the seperator?

  4. #4
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Select "Fixed width .........." and go on.

  5. #5
    Join Date
    Mar 2003
    Posts
    225
    ok i have that done, i now a number of columns of data in my table...
    i am now appending the data but i need this split into the four as listed above, really dont get this

    please help

    I have attached the imported data in the database, but i cant seem to join the feilds together
    Attached Files Attached Files

  6. #6
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    When you select "Fixed width..." in "Link Text Wizard", click on Next button,
    and set up a borders of fields (wher you want), read what is written on the
    "Link Text Wizard" Form.

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    It's not that simple. The problem is that because the date field's width changes, fixed width importing doesn't quite cut it. When the parts of the date and time are using all digits (like with months with two digits and times with the hour is two digits) it's going to cause havoc with a fixed width import.

    Perhaps the least complicated way is to import with a space delimiter and then concatenate the results after the import.

    Another way is to import the lot into one field, and then use Access string expressions to split it up where it detects " AM " or " PM " (note the spaces). It's a bit fiddly, but at least it will work for any time and date.

    And yet another way is to do a character by character import with code and wait until the first "M" is detected, then split it there.

    Which way to go is ultimately up to you and what your needs are... if it's just a one-time import or whether this has to be done again and again with more incoming data.

    BTW, I see only two fields required here, the date/time field and the problem field. The parts of the date and time can be generated with a query if you need them later.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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