Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    53
    Thanks
    2
    Thanked 1 Time in 1 Post

    Append without first record?

    Is there a way that I can write a query to allow me to append all EXCEPT the first row(s) of a table to another table?

    For a brief background, I am analysing NT perfmon data, and the first record of each file (actually a .csv file) is usually corrupted so that I need to ignore it. My Access97 code links the CSV file as a table, and then I use an SQL INSERT statement to pull out the required data and append it to my database table.

    Just to make it more complicated, I need to link to the .CSV file without 'field names in first row', as the (standard) names are not valid field names for Access. Hence my INSERT statement already has a WHERE clause that excludes any row that does not look like valid data. What I need to exclude is the first row that "looks" like valid data.

    Unfortunately it is not simply a matter of just remembering the time stamp of the first record and then later searching for and deleting it, as there may be already be a valid duplicate record with the same timestamp.

    I have a feeling there is a 'lateral thinking' way to attack this one, and would be grateful for any suggestions.

    Thanks.
    Geoff

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Append without first record?

    I'm having a little trouble understanding how you can have a record that apparently has valid *fields* but is corrupted. What does corrupted mean in this situation?

    And how do you identify the record. Is there something in the record you could use to filter it out? Is it empty?

    Access doesn't really care about what order the rows are in because relational tables use indexes to determine order, so you can't tell it to just exclude the "first" record. There has to be some other method for identifying the record.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append without first record?

    A little kludgy, but perhaps you simply import the .csv file allowing Access to generate an Autonumber ID for each record. The first record in the .csv file should always have an ID of 1. Next step is to either (1) delete the records with ID = 1, or (2) Append the data into a "repository" type table, excluding records with ID = 1.

    Not particularly elegant, but should be brutally effective. Good luck!
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  4. #4
    Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    53
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Append without first record?

    Thanks for the responses, Shane and Charlotte.
    Shane, your approach sounds helpful. It provides the unique identifier I would need to dinf and delete the record, at the expense of another append of the data to a temporary table.
    Charlotte .. no, there s nothing unique in the data, it is just that experience shows it contains data values which are not legitimate (eg, CPU utilisation close to 100% or wildly inaccurate paging, memory value, etc).
    Thanks.
    Geoff

  5. #5
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append without first record?

    Geoff,

    re: ".. I am analysing NT perfmon data... " (skip 3 msgs) " .. it is just that experience shows it contains data values which are not legitimate (eg, CPU utilisation close to 100% .."

    This reply is a year after your original posts -- but I was intrigues because, while I've been looking for some information on a 100% CPU usage issue I am having, I stumbled across a KB on 100% CPU usage on Microsoft that had "perfmon" in the topic -- that, and combined with your CPU comment in a later message, I thought I'd just pass that on to you (fwiw, you may not be dealing w/ this issue any more).

    thx
    Pat

Posting Permissions

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