Append without first record?

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

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

Reply | Quote