Text File Link

[pkell658]

Greetings. Total noob here. I have a unique issue that I've never encountered before. I am working with monthly data that in raw text requires about 6 gigabytes to store in 12 delimited text files. I originally had twelve separate monthly files that I appended into two separate six month files. To do this append, I used the copy command from the DOS command line. That may be an issue. However, assuming that the DOS command simply appended the files together properly, I believe I have an Access issue using the linked tables. Once the files were combined into 2 files, I linked them into Access with a delimited import spec.

Here is the first level issue: When I query the linked tables, Access does not reflect the actual values of the raw text file.

Example: One record has a value of 17,627,310 in the linked table but an actual raw file value of 17,627,314.0

I have the linked tables set up to link that particular field as a Single data type. I cannot open the raw file using any text editor because the file is 3 gigabytes, but the individual monthly file can be viewed and shows a value of 17,627,314.0

Here is where the second-level issue occurs: If I run a GroupBy aggregation on the linked table, the value is summed properly as 17,627,314 like the raw file. However, if I turn off aggregation and view the data and/or copy the values to the clipboard, the value returns to 17,627,310

I should also point out that the reason I am using the linked tables is to avoid the 2GB restriction in Access. Also, I originally tried a master 6GB text file linked table, but Access could not read the data from the file. Is there a specific GB linked text file limitation that may be contributing to this problem? The two 3GB links seem to be working but for this issue.



Has anyone seen this before? Why would Access use a different value in table view or through a select query but find the actual value when aggregating the data in a GroupBy? I am frightened by this issue because there is no error that is being thrown during the import/linking process or via queries. I just happened to run a back-end check to my GroupBy query by pulling the detail data. Should I stay away from these links and create multiple databases with hard imports of the data?

Any help is greatly appreciated. Best.

[Gerry]

This is mystifying. My first reaction is that it appears to be rounding. How many records does this happen with? If you still have the monthly files, you might try linking to the one that has this same piece of data, to verify if the problem was caused by the aggregation of the separate files. Also, I'd try a Make Table query to import a few hundred records into Access to see how they turn out - just to give you a chance to view that data in a table, and check data types, etc.

Also, have you tried opening the text file with MS Word? It may be capable of opening a file that length, though I've never tried.