Is objStream.ReadLine always in correct order? (1 Viewer)

[bignose2]

Hi,

Just wanted to check before I got to lengths programming (as testing is not 100% & annomolies can show up at any time)

Is using the FileSystemObject & .Readline 100% sure to read line by line in the order of the file. In my case a .csv

TransferText is not guaranteed as far as I can tell.

For ages always had problems trying to import CSV's or TXT files in the order they originally created. Often no field in true sort order, no autonumber or unique field.

I know the whole point of a database if to make sure you have these fields but not always easy with files from other sources.

Transfertext often works not not guaranteed. I read linking is good but still not so sure of this.

Best example is Bank Statements exported to be used in accounting or others.
Always have a date of course but if the same day, e.g. 30 on the same day they can get mixed up & trying to visually match from original source to imported is a pain.
I have other situations with text files also so would be great if I can rely on this

I can read line by line & add my own autonumber as it goes, also not including lines I don't.

Also seems pretty fast this way but not tested much yet.

Thanks I/A

 

[CJ_London]

It is not so much the reading that matters, it is the writing. It may be read in the correct order but the table will be populated randomly. Basically there is no sense of order in a table - you use a query to generate an order. So I don't think it matters what basis you use, you will still have the same potential issue.

You may find it helps to include an autonumber field in your destination table since that should be generated in the order of insertion.

With your bank statement example, for same day entries it may be presented in date order, but then presented randomly within a day. You would also need a time element or perhaps some other basis - credits before debits, type of transaction for example which you can perhaps determine by analysing the source to use as an order in a query.

Not sure why you are trying to visually match to original source - you can use check queries (perhaps sum debits/credits) to confirm you have imported all transactions.

 

[The_Doc_Man]

Utilities such as Word, Wordpad, Notepad, and Excel do not randomly re-order items, though some of those programs have sorting procedures. However, in the absence of such operations, they present in the order that things were stored. Of course, there is the single exception that you can move the cursor and perform a random-location insert. Those random insertions will remain in the order that you imposed by selecting the insertion point.

Access is different because of updates and insertions that can force a re-ordering of the table. I'll spare you the techie explanation, but there is a good reason for that. So if you have no intrinsic ordering in a query, and certainly if you have no intrinsic ordering in a table, you would get records back in an order potentially different than the order in which the records were written. The ONLY time you are likely to retrieve data in the order that the records were originally written would be if you NEVER performed an update on that table, because in that case the sequence of events that causes table re-ordering doesn't occur. But even there, I wouldn't hold my breath.

Now let's address the REAL problem. I offer a rule or two that is important to understand when designing a database. One of them applies.

Old Programmer's Rule #2: Access won't tell you anything you didn't tell it first.

If the information you are processing needs to be viewed in a certain order, you must take steps to assure that there is a way for Access to REMEMBER that order in each record, and then use and ORDER BY clause in a query to present the records in that order. So if there is an apparent randomization of order, this is a design flaw, not a bug-type problem with Access.

 

[Cronk]

One method would entail importing CSV data into Excel, adding a column and populate it with ascending numbers, then import the Excel data into Access with the generated (ordered) numbers.

 

[essaytee]

Hi,

... snip

Is using the FileSystemObject & .Readline 100% sure to read line by line in the order of the file. In my case a .csv

... snip

I would have thought that reading a file line by line would be in the order of the opened file. You could easily test this by creating a text file each line a number, incremented by 1. Test it for 10 lines or whatever.

How it ends up in the table is neither here nor there but I would have thought some form of unique reference is added.

 

[The_Doc_Man]

essaytee, the unique reference is added if and only if you define the table with an autonumber PK. Otherwise, Access doesn't do anything to mark the order of input. Nor do most text utilities, unless you force them to do so.

Oddly enough, Access itself DOES add unique (but usually invisible) line numbers - to VBA module lines. However, if you use the mouse to insert lines in the middle of the module, everything gets renumbered.

 

[essaytee]

essaytee, the unique reference is added if and only if you define the table with an autonumber PK. Otherwise, Access doesn't do anything to mark the order of input. Nor do most text utilities, unless you force them to do so.

Oddly enough, Access itself DOES add unique (but usually invisible) line numbers - to VBA module lines. However, if you use the mouse to insert lines in the middle of the module, everything gets renumbered.

Hi Doc, yes I'm aware of that, I concentrated my response on the reading of the file, that it would most likely be read in sequential order. However, how it ends up in a table will require some form of manipulation if you want the same order.