Transfertext to .txt file

[Robert Nusz @ DPS]

I've got a Ms/2003 database with a table that has 990,000 + records in it.
Is there a way to get the data out of this table and into a flat file named
mytable.txt or mytable.dat where it will be comma delimited and have all
990,000 records in it, every thing that I try terminates at about 70,0000 +/-
records. Is there a limit as to how many records can be exported. Is there
a way to code and get all records sent to a .dat or .txt file, if so, any
examples out there would be greatly appreciated.

Thanks,

 

[Dave Bolt]

I would have thought a problem with the size of the text file. You did not
say the average number of characters in a line so could not begin to guess
beyond the minimum size of a file to contain 990000 records would be
990000*3 bytes, one per record plus two per new line/carriage return
combination.
You could try creating a number of smaller tables each containing less than
70,000 (or should that be 700,000) records, export each to a separate text
file. Once you have the files you can try to join them together, if they are
not too big.
HTH
Dave

 

[Robert Nusz @ DPS]

Dave,

Thanks for the response. Sorry about the lack of information. I've
actually got three tables going right now. One table has over 990,000
records in it, and the record length is just under 200 bytes per record.
Huge table but its necessary. The table holds all the data, the data appears
correct, but problem is that I need to extract from the table to a .dat or a
..txt file inorder to all the data to be ftp'd up to an oracle application.
Evidently, our contractor can't handle data from Microsoft Access up into an
oracle application. Anyway, they wanted me to either create a .txt file or a
..dat file, or to create it as an GJXDM output file, I'm guessing with data
tags on each field of data. Ever worked with GJXDM?

THanks,

Robert

 

[John Nurick]

Hi Robert,

The ordinary export-to-CSV routine (File|Export and DoCmd.TransferText)
doesn't limit the number of records. I've just now exported 999,200
records each of more than 200 bytes from an Access table to a text file
that ended up at more than 210 megabytes, and confirmed that it contains
999,200 lines.

 

[Dave Bolt]

Based on John Nurick's reply I would suggest that you try repairing and
compacting your database before exporting. You could also try exporting to a
new table. That should either fix the problem or hopefully give more
information about what is going wrong.
HTH
Dave

 

[John Nurick]

Hi Robert,

Don't count on record length being unlimited. There is a limit (maybe
64k bytes, I can't remember) when importing from text files, but I don't
know whether it also applies when exporting.

This is distinct from the record length limit in Jet (.mdb) tables.

 

[YRUTYYRTRGF]

Hi Robert,

The ordinary export-to-CSV routine (File|Export and DoCmd.TransferText)
doesn't limit the number of records. I've just now exported 999,200
records each of more than 200 bytes from an Access table to a text file 04424758875400
that ended up at more than 210 megabytes, and confirmed that it contains
999,200 lines.