CSV record order

[namliam]

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.

I think very small in this case (assuming you have a seperate import table that you do not use for anything else) is an overstatement.

I agree there is some form of risk using an autonumber, but 99.9% of all cases it is sequential and 100% full proof in this type of situation.

There is an alternate problem which the OP may not be aware of... The nature of a query in any database is RANDOM by default and definition.
That is UNLESS there is a specific "order by" statement in the query beeing given to the OP, the order of the query is RANDOM.
Again in 99+% of all cases it will be the order of data-entry that is returned, but there is NO GUARANTEE, unless you have an explicit order by statement.

Even if you have then an order by on the date/time of the record, there is no guarantee you will get the right order for date/time that are equal to eachother.

NOW!!! NOTE !!! This is basicaly exception of the exception that by exception will cause an exception, but NO GUARANTEE can be given unless you can GUARANTEE IT.
If the order in the file is 100% guarantee... then there must be some PK there with an ORDER BY... else people are BS-ing you

 

[MailMan]

Thanks to everyone who gave advice on this problem.

That is UNLESS there is a specific "order by" statement in the query beeing given to the OP, the order of the query is RANDOM. Again in 99+% of all cases it will be the order of data-entry that is returned, but there is NO GUARANTEE, unless you have an explicit order by statement......

If the order in the file is 100% guarantee... then there must be some PK there with an ORDER BY... else people are BS-ing you

100% guarantee on the order of the csv. I suspect the original data is kept to the thousandth of an hour in the Oracle db but is not available to that precision in the canned queries.

To sum up:
Csv will be imported with autonumber to temp table.
temp table will be split into 3 tables including clock rings table.
In the clock rings table, autonumber will be combined with unique identifier to identify record order.

 

[namliam]

The sum up sounds good.

 

[gemma-the-husky]

i am not sure that importing a csv necessarily (via docmd.transfertext) imports the records in the same order they were in the csv - in fact from memory, i would venture that this cannot be relied upon.

as far as access is concerned the order is immaterial. so if there is no way of reconstructing the order based no the data, then there is a problem.


therefore it seems to me that there are two solutions

1. put a sequence number in the csv file
2. import the file by reading it as a text file, and parsing each row manually

 

[boblarson]

i am not sure that importing a csv necessarily (via docmd.transfertext) imports the records in the same order they were in the csv - in fact from memory, i would venture that this cannot be relied upon.

I do believe you are correct about this Dave.