CSV record order (1 Viewer)

MailMan

Registered User.
Local time
Today, 18:50
Joined
Sep 7, 2007
Messages
20
Is there a way to import a csv file and include the row order of the file? I have a csv file that includes employee clock rings where I need to be able to identify which clock ring was first. While the clock time will usually provide this, sometimes an employee will hit the clock twice in the same time period (hundreths of an hour) and there is no way to determine which ring was first except for the order within the csv file. I need a way to add the row number to the access table. This file will be overwritten twice a day and can be as large as 400,000+ rows in one week. The reason the order is important is that the employee's task of record will be determined by the later ring. I don't want to use autonumber since it can't gaurantee the numbers will run lowest to highest.
 

Guus2005

AWF VIP
Local time
Today, 23:50
Joined
Jun 26, 2007
Messages
2,645
When you import a CSV file into Access(existing table with autonumber field), it is done sequentially and in doing so it creates a sequential number each one higher than the previous one. This never fails.

I don't want to use autonumber since it can't gaurantee the numbers will run lowest to highest.
I am not sure what you mean by that. I have never seen otherwise. (15yrs experience.)

HTH:D
 

MailMan

Registered User.
Local time
Today, 18:50
Joined
Sep 7, 2007
Messages
20
According to what I have read on this forum, autonumbers should never be used for anything except as an index. Bob Larson writes:

"Do not use autonumbers for meaningful purposes. You WILL get burned at some point. Autonumbers are guaranteed to give you a UNIQUE number. They are not guaranteed to go in consecutive order (even if you do have that property set) and they are not guaranteed to always be positive numbers. Autonumbers should really only be used for "behind-the-scenes" stuff. If you have need of a specific number with meaning, use a different method (the DMax + 1 for example)."

I actually have the code to use the autonumber (even resetting the autonumber each time) but don't want to use it because of what I have read here. I need to keep and retrieve the record order of the csv file to do a calculation on time difference between records.
 

Guus2005

AWF VIP
Local time
Today, 23:50
Joined
Jun 26, 2007
Messages
2,645
It is a guideline.
I agree on the part where autonumber should be used behind-the-scene.

I'd appreciate a link to Bobs message.
 

namliam

The Mailman - AWF VIP
Local time
Today, 23:50
Joined
Aug 11, 2003
Messages
11,696
If you have the time in records, cannot you order by that?

Autonumbers will not guarantee this on an average table, using it on a csv import will do the job just fine. If you feel you dont want to you can write your own import process that pick up the file line by line adding your own sequential number. A bit more work, a bit (or actually considerably) slower, but 100% guarantee to work the way you want it to, cause you make it...
 

MailMan

Registered User.
Local time
Today, 18:50
Joined
Sep 7, 2007
Messages
20
If you have the time in records, cannot you order by that?

99.9% of the time I can. But employees sometimes do hit the clock twice in the same time period and there is nothing in the record itself to show which happened first. Only the order within the file itself shows this.

Autonumbers will not guarantee this on an average table, using it on a csv import will do the job just fine. If you feel you dont want to you can write your own import process that pick up the file line by line adding your own sequential number. A bit more work, a bit (or actually considerably) slower, but 100% guarantee to work the way you want it to, cause you make it...

If it will work with a csv file that is fine with me. I don't want to go line by line (400,000+ lines) because of performance issues. I will use the autonumber then. Thanks for the clarification!

And on the side-I guess by your name that I am your mirror image.:D
 

MailMan

Registered User.
Local time
Today, 18:50
Joined
Sep 7, 2007
Messages
20
Link to Bob's quote:

To both irunergoiam and Fisher31 -

Do not use autonumbers for meaningful purposes. You WILL get burned at some point. Autonumbers are guaranteed to give you a UNIQUE number. They are not guaranteed to go in consecutive order (even if you do have that property set) and they are not guaranteed to always be positive numbers. Autonumbers should really only be used for "behind-the-scenes" stuff. If you have need of a specific number with meaning, use a different method (the DMax + 1 for example).
 

namliam

The Mailman - AWF VIP
Local time
Today, 23:50
Joined
Aug 11, 2003
Messages
11,696
99.9% of the time I can. But employees sometimes do hit the clock twice in the same time period and there is nothing in the record itself to show which happened first. Only the order within the file itself shows this.
I am probably missing the point, but if the timeframe would be the same and you are trying to calculate differences in time... THEN WHO CARES??

If it will work with a csv file that is fine with me. I don't want to go line by line (400,000+ lines) because of performance issues. I will use the autonumber then. Thanks for the clarification!
It should work just find, assuming you use a default autonumber... The problem with normal tables where you have (semi-)random data entry/manipulation is things get mixed up, with data import of a csv I dont think anything can go wrong...

And on the side-I guess by your name that I am your mirror image.:D

Well, no I am the one the only the original the best the fastest the most flexible and helpfull Mailman there ever was and there ever will be (barring Carl Malone, who is/was just in a league of his own).

I started reversing my name years ago when to many people were trying to steal/borrow/copy my name. I was semi-unique in this for a good while untill more started showing up :(

/smugg self promoting comments
 

MailMan

Registered User.
Local time
Today, 18:50
Joined
Sep 7, 2007
Messages
20
I am probably missing the point, but if the timeframe would be the same and you are trying to calculate differences in time... THEN WHO CARES??

The operation the employee rings on may be different. The type of ring may be different. But the time is the same. The last one is the live ring. Need to know what operation for the calculation with the next record.

Well, no I am the one the only the original the best the fastest the most flexible and helpfull Mailman there ever was and there ever will be (barring Carl Malone, who is/was just in a league of his own).

I started reversing my name years ago when to many people were trying to steal/borrow/copy my name. I was semi-unique in this for a good while untill more started showing up :(

/smugg self promoting comments

I came about mine honestly-it used to be my actual job!:cool:
 

Banana

split with a cherry atop.
Local time
Today, 15:50
Joined
Sep 1, 2005
Messages
6,318
If there is only one person to do the csv import, I think we can 'cheat' and use autonumber just fine. The problems arises when we delete some rows in process or update a row to be "later" without updating the autonumber itself. Since you say time component is not sufficient, I'd just add two more column, a autonumber for this one import, and another number column that's DMax+1 or maybe just a date for today import, so for each import, there's an unique import number/import date combined with autonumber indicating which row came first, next...

But if there could be more than one people doing the import, or the import could be repeated, then this may be more tricky.
 

boblarson

Smeghead
Local time
Today, 15:50
Joined
Jan 12, 2001
Messages
32,059
Yes, it is true that an autonumber CAN go wonky and not increment, even if sent to increment. It is true that it can be a negative number (mostly when Random or Replication is set). So the point is valid. If you MUST have an order that is incremented and not negative or some large integer, you should use your own numbering. Most of the time it will be fine to use an autonumber for numbering records, but you shouldn't even need numbering IF you have a DATE AND TIME stored. Then it can be ordered by Date/Time. Using TIME ONLY, that is a problem and even importing from CSV files CAN put the data out of order of the CSV file.
 

MailMan

Registered User.
Local time
Today, 18:50
Joined
Sep 7, 2007
Messages
20
Yes, it is true that an autonumber CAN go wonky and not increment, even if sent to increment. It is true that it can be a negative number (mostly when Random or Replication is set). So the point is valid. If you MUST have an order that is incremented and not negative or some large integer, you should use your own numbering. Most of the time it will be fine to use an autonumber for numbering records, but you shouldn't even need numbering IF you have a DATE AND TIME stored. Then it can be ordered by Date/Time. Using TIME ONLY, that is a problem and even importing from CSV files CAN put the data out of order of the CSV file.

My problem is that an employee can have two clock rings with the same Date and Time (two separate fields) with nothing in the record itself that shows which of the two were first. The only way to determine this is by the order in the csv file. Time is kept in hundreths of an hour (not minutes). There are no thousandths (which would solve the problem). When first imported, it looks like Access shows the table in csv order (even without autonumbers). I need a way to record this order into the record. Is Namliam's suggestion of importing line by line and adding a sequential number the only way to do this?
 

boblarson

Smeghead
Local time
Today, 15:50
Joined
Jan 12, 2001
Messages
32,059
Is Namliam's suggestion of importing line by line and adding a sequential number the only way to do this?
I think that is exactly the ONLY way you can GUARANTEE that the order will be correct.
 

Banana

split with a cherry atop.
Local time
Today, 15:50
Joined
Sep 1, 2005
Messages
6,318
Probably so, but I can't imagine it being effective. If the OP has time to whittle away, then that's all well and good. Otherwise, I'd say just use autonumber & dump into a temporary table, strap on a unique import data and insert into the permanent table. This should work as long there's only one person doing the import.

A far better solution would be to edit the CSV format from where it came, if at all possible.
 

MailMan

Registered User.
Local time
Today, 18:50
Joined
Sep 7, 2007
Messages
20
A far better solution would be to edit the CSV format from where it came, if at all possible.

I can't edit it from where it came (query from company mainframe through an oracle user interface), but I could edit it before importing, if that is possible. Or could a link work?
 

Banana

split with a cherry atop.
Local time
Today, 15:50
Joined
Sep 1, 2005
Messages
6,318
Wait, wait. Oracle? Surely you could get the row_num() pseudo column from that, no? I'd want to double-check with whoever is administering this and make sure this is in fact impossible.

As for editing before importing, I was thinking of importing into a temporary table with an autonumber column & unique import identifier before inserting into the final table.
 

MailMan

Registered User.
Local time
Today, 18:50
Joined
Sep 7, 2007
Messages
20
Wait, wait. Oracle? Surely you could get the row_num() pseudo column from that, no? I'd want to double-check with whoever is administering this and make sure this is in fact impossible.

I have no way to do this. Oracle db is at national level. I'm at district level. I can only get data through canned queries. If I could query the Oracle db myself, I wouldn't need to do it this way to begin with. Right now, I use 5 different queries that do not provide as much data as this one query would.

As for editing before importing, I was thinking of importing into a temporary table with an autonumber column & unique import identifier before inserting into the final table.

That's the way I had set it up originally. It is imported into temp table and then split into 3 other tables. This would still rely on the autonumber for row order, which from reading Bob Larson's replies, is what I would like to avoid.
 

boblarson

Smeghead
Local time
Today, 15:50
Joined
Jan 12, 2001
Messages
32,059
That's the way I had set it up originally. It is imported into temp table and then split into 3 other tables. This would still rely on the autonumber for row order, which from reading Bob Larson's replies, is what I would like to avoid.
Just so you are aware, you should not have any trouble doing this with an autonumber. Sorry I have scared you, but the odds of it NOT working as planned are actually very small. But you do need to be aware of the risks.

If this is a mission critical system, then you need to work with management to get the people who manage the output process to give you primary keys.

If not, then I say go for it using Banana's suggestion.
 

Users who are viewing this thread

Top Bottom