Split A Query Into Two Text Files?

[kestefon]

I have a very large query that I need to export to text. I'm hitting the 4GB limit, so I'd like to "split" the query and create two text files instead.



What's the most efficient way to do this? Also, are there any export file formats that can handle more data? (i.e. is there a format that could fit this massive query into a single file?)

[ssanfu]

From what I have found, In Win7 using NTFS, there is a file limit of 16 TB. So the limit would be how much hard drive space you have available.
However, if the data is being written to a hard drive formatted as FAT32, there is a file size of 4GB.

From http://stackoverflow.com/questions/3...on-a-text-file

There is no limit, other than the size of your disk, and your file system limitations on a file.

For example, file size limits:

• NTFS: 16 TiB - 64 KiB
• Ext4: 16 TBs
• FAT32: 4GB - 1

On disk, there is no difference between a text file and any other type of file. They all just store bytes of data.

Some text editors tend to choke when the text file is very large... (ie windows Notepad)
IIRC, Notepad++ will handle very large text files.

What's the most efficient way to do this?

If you want to split the data into two files, add criteria to the query. Sort the query and add criteria that selects about half the data.
For example, if you have a data field with a min date of 01/01/2000 and a max date of 12/31/2013,
Set the criteria for the date field to <= #06/30/2006
That gets the first half (approx) of the data.
Then set the criteria to > #06/30/2006 to get the rest of the data.

are there any export file formats that can handle more data?

From what I have read, a text file should be able to handle the export.
I would check the drive format to see if it is formatted as FAT32.

[kestefon]

Thanks for the detailed response. I checked my drive format and it is NTFS. Any idea why I'm running into the limit?

[June7]

How much installed RAM? I would expect more than 4GB on a new system.

[ssanfu]

At this point, no. Sorry.

I don't know anything about your project, the query or how you are exporting the data.

Do you get error messages?

[orange]

We have to know more about your query. Can you please post the sql?

If you have some identifiers in your data, say OrderNumber ranges from 20 to 1,000,000 in your table/query.
You could see how many records would qualify for export by seeing how many Orders have OrderNumber <250000 (or you choice).
A few of these then you could select where OrderNumber < 250000 and export as Set1,
then where orderNumber between 250001 and 600000 (or your choice) and export that as Set2

You could divvy up you full export into partial sets, and bring them together later.

Adjust the values to suit your conditions.
Good luck.

[kestefon]

I've decided to just add "WHERE tbl.date BETWEEN #x# and #y#" to the SQL in order to export two queries, so that problem is solved...

As for the NFTS thing, here's the specific error message I'm getting from the Access Export Text Wizard: Reserved Error (-1); there is no message for this error". When I click okay it then tells me "An error occurred trying to export data from [table]. The data was not exported." I don't think the error message is happening exactly at 4GB, but shortly after the file passes that point it crashes.

[orange]

If you think it's a volume of data issue, then try making 3 exports by tightening your x and y dates.

Hopefully you have tried exporting say 100 recods to ensure that an export does occur. Make sure the basic process works; then adjust your parameters to get all the data exported. Do you really care if 3 or 5 exports are needed to get all the data exported?

[ItsMe]

4GB sounds about the size of a string. Perhaps there is a correlation

[June7]

Not sure how string length applies but I just read a source that says 2GB.

A memo field can take 1GB when entered programmatically.

[ItsMe]

Maybe I am thinking variant at 4BG. Regardless, there seems to be a relevance with 4GB; could be allocated virtual memory for the app (Access). Maybe a print to file procedure is in order. Create 2 or 4GB chunks create a filesystem object and do a bunch of write lines. Maybe find a way to merge many 4GB files. Maybe find a way to write to the same filesystem object without closing it, picking up the procedure again after each 2GB or 4GB chunk. I thing an object like a file system object is bigger than 4GB. Orange pointed the fact out fairly distinctly. The OP is going to have to find the limit for export and work with it.