Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2012
    Posts
    5

    Exclamation Unanswered: Importing data from text file

    HI,
    i want to know if there is any way to import data from a text file
    the condition is that the
    1.Data starts with $DTR and ends with # (there is lot of unwanted text which should be discarded)
    2. the text file will be updated ever 5 minutes so we need to take only the data which which was inserted in the text file in the past 5 minutes (discarding all the previous data and the data which is out side $DTR,...,#)

    i am new to this field
    it would be very helpful if u can elaborate ur answers
    even better if u can help with the code

    THANK U FOR UR REPLIES....

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    probably best to write a VBA process for this, noit for the feint hearted.

    or

    import the file into a holding table, then reporcess the rows int he holding table

    providing you make some for of primary key out fo the data then you should be bale to stop rows being duplicated
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Getting the data from the text file and into the database should be manageable, but you need to know how you choose the data to import:

    How do you determine what was added to the text file in the last five minutes?
    When is the source text file purged?
    Is there a timestamp on records or an ID field in the text file?
    Is there anything that uniquely identifies a row of data?
    What format is the source data in? Is it a continuous stream or one record per line?
    Is everything between $DTR and # going into one database field or does it need to also be parsed?

    Steve

  4. #4
    Join Date
    Jun 2012
    Posts
    5
    Quote Originally Posted by sps View Post
    Getting the data from the text file and into the database should be manageable, but you need to know how you choose the data to import:

    How do you determine what was added to the text file in the last five minutes?
    When is the source text file purged?
    Is there a timestamp on records or an ID field in the text file?
    Is there anything that uniquely identifies a row of data?
    What format is the source data in? Is it a continuous stream or one record per line?
    Is everything between $DTR and # going into one database field or does it need to also be parsed?
    thanks for responding
    the records keep on adding to a new file everyday and the file name will be that day's date
    the text file contains serial number, date, time, current, voltage....
    and the data feeds in the text will be like
    $DTR, 123-456-7890, 12/6/12, 12:09:00, 123,456,,.....,,#
    each data feed will be in a new line(whole line)
    and some comments may be written between 2 feeds

    Eg:

    $DTR, 123-456-7890, 12/6/12, 12:09:00, 123,456,,.....,,#
    $DTR, 123-456-7891, 12/6/12, 12:09:00, 124,457,,.....,,#
    some comments
    some more comments
    $DTR, 123-456-7890, 12/6/12, 12:19:00, 125,458,,.....,,#

    the data should be appended to a single table in a db which is auto numbered
    it would be very helpful if you can help us with the code as our project deadline is approaching and we are stuck with the vba part as if we dont have previous experience

  5. #5
    Join Date
    Jun 2012
    Posts
    5
    Quote Originally Posted by healdem View Post
    probably best to write a VBA process for this, noit for the feint hearted.

    or

    import the file into a holding table, then reporcess the rows int he holding table

    providing you make some for of primary key out fo the data then you should be bale to stop rows being duplicated
    thanks for responding could please help us with the code we are new to vba

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why autonumber
    isn't the dtae and time of the reading sufficient to make a row unique
    Im guessing you will never change the date and time of a recording (unless say there was a screw up over standard / daylight saving time.

    making the datetime of the recording (+presumably the instrument making the recording) the primary key should stop the possibility of duplicate rows.

    as suggested before import the data into a temporary table. then use an insert query to transfer rows to the live table. duplicate rows will be rejected with a warning, so you could be a smart aleck and only copy those rows which aren't already in the final table. look at the query wizard to see an example of how to find rows that are not already in another table.. again its the primary key that is doing the graft in this problem
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jun 2012
    Posts
    5
    Quote Originally Posted by healdem View Post
    why autonumber
    isn't the dtae and time of the reading sufficient to make a row unique
    Im guessing you will never change the date and time of a recording (unless say there was a screw up over standard / daylight saving time.

    making the datetime of the recording (+presumably the instrument making the recording) the primary key should stop the possibility of duplicate rows.

    as suggested before import the data into a temporary table. then use an insert query to transfer rows to the live table. duplicate rows will be rejected with a warning, so you could be a smart aleck and only copy those rows which aren't already in the final table. look at the query wizard to see an example of how to find rows that are not already in another table.. again its the primary key that is doing the graft in this problem
    auto number is required for other part of our project
    thanks for the help

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    nope
    an autonumber is never required as part of thios or any other project.
    An autonumber is required in a realtional db schema if there is no other natural key. an autonumber is used to generate a unique value, a value that uniqeuly idenitifies that row. the date time does that for you.

    need the rows in sequence, not an issue use the order by. bear in mind that Access stores date time values as a number.. its guaranteed to be unique if you make it the primary key. if you really really must have an autonumber column, then merely defiene an index on the date time and set its properties to unique
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jun 2012
    Posts
    5
    Quote Originally Posted by healdem View Post
    nope
    an autonumber is never required as part of thios or any other project.
    An autonumber is required in a realtional db schema if there is no other natural key. an autonumber is used to generate a unique value, a value that uniqeuly idenitifies that row. the date time does that for you.

    need the rows in sequence, not an issue use the order by. bear in mind that Access stores date time values as a number.. its guaranteed to be unique if you make it the primary key. if you really really must have an autonumber column, then merely defiene an index on the date time and set its properties to unique
    there can be feeds with same date and time and have different serial_number(a text value referring to the device address which posted the feed on to the txt file)
    thus i used an auto number feed (feed_id) for referring the feeds since considering date time and serial number together as a primary key might lead to problems while handling the data

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    don't see why it should be an issue

    if you have concerns over the length of the primary key then that is a reasonable justification for an autonumber column. But that doesn't stop you defining a unique index comprised of the date time an event took place and the device that reading belongs to to make certain a sepcific readin is uniquely stored in the DB. whether you choose to use an autonumber for the device ID is up to you.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Although a natural key is preferred if one is readily available, a PK key on a single auto number field is much easier, and safer, than a composite key if the PK of this table is referenced in other tables. As previously mentioned, create a unique index on the table using your natural composite key even if those fields are not used at the PK.

    back to the original question about importing...

    Import the file into a temporary table that has all of the fields you need plus a flag field at the beginning to catch the $DTR value. If there is always a comma before the ending # then add another field to catch that as well, otherwise you'll need to filter it out during the update query.

    Once the data is in the stage table run an append query into the live table where the flag field = $DTR. Since you have the timestamp in the file I would store this in the database to filter down the next load to only new records.

    Steve

Posting Permissions

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