Exporting from a query to multiple text files

  • Thread starter Thread starter Troy D. Young
  • Start date Start date
T

Troy D. Young

I have a table with about 122,000 records in it. I need to process it with
an application that has a limit of 10,000 lines per text file. So, I need
to somehow set up a procedure to export the data and chop it up into
separate files. In this example I will need for it to produce twelve 10,000
record files and one 2,000 record file. I know that it would be easy for me
to just do it manually, but I need to set up this capability for the client
so they can do it easily without manual interaction. They will be
periodically importing text files of different sizes and I need for them to
be able to click a button and spit out the data in this manner, no matter
how many records it is.

Can anyone give me a clue how I would do this? I assume that it will have
something to do with counting the records. I am hoping this can be done
with some type of query and macros, as I am not much of a coder.

Thanks,
Troy D. Young
 
Dear Troy:

Several ways of doing this come to mind. All of them would be based
on some way of ordering the data, preferably uniquely.

Would there be any kind of very meaningful way of dividing the file,
perhaps by the name of a person, city, or state? That is to say, what
if all the rows where this "name" starts with A were gathered into one
file, B in another file, and so on. If you have 26 files for each
letter of the alphabet, plus one more for any rows where the "name"
does not start with a letter of the alphabet (space, punctuation,
digit) it is highly probably that none of these would have more than
10,000 rows of data. And, if there is any advantage to that kind of
organization, then you could enjoy that benefit.

A disadvantage to this is that, with changes in the data or an
increase in the number of rows, there could come a time when one of
the letters represents more than 10,000 rows of data.

Based on some unique sort key, you could also use the TOP predicate to
break out a specific number of rows, perhaps 10,000 or maybe just less
(for safety), say 9,900 rows. To do this in a query, the first set
would be:

SELECT TOP 9900 * FROM YourTable ORDER BY SortKey1

For the second set:

SELECT TOP 9900 *
FROM
(SELECT TOP 112100 * FROM YourTable ORDER BY SortKey1 DESC)
ORDER BY SortKey1

However, the math for the inner TOP value would need to be done in
code. After counting the total number of rows, you would need to
generate the query dynamically (write the SQL text in code and run it
in code) as this would change when the number of source rows changes.

The column (or set of columns) represented by SortKey1 would have be
be unique, perhaps using your Primary Key to the table.

The disadvantages to this approach are that it is more work, and
demands a guaranteed unique key to the rows in the source table.

Perhaps one of these has some appeal. If you can choose one and need
more elaboration on it, please let me know.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
This is a list of data from hospital patient visits, so almost every field
is different, obviously with a small percentage of them as duplicates from
repeat visits. The "Visit ID" field would be unique. Only certain fields
would be duplicated, such as name, SSN, etc. I was hoping there would be an
easy way to do it, such as export the data until "record count=10000" is
reached, mark a field called "exported=1" as such for that 10,000 records,
then loop the process until all records are exported and the record count
reaches zero for the "exported=0" parameter. Of course, the exported file
names would have to be unique or incremented.

Again, I'm just saying how I would like to see it work, not that it can be
done this way. I know....code, code, code. If it weren't for code, I'd be
one helluva programmer :)
 
Dear Troy:

"I know....code, code, code. If it weren't for code, I'd be one
helluva programmer :)" You got a laugh here, guaranteed!

Well, the coding for the queries would be very much as I suggested.
Form what you have told me, you could replace my "SortKey1" with
"[Visit ID]". But you've still got some coding to do.

I really didn't see where you had considered the relative merits of
the two approaches I suggested. Unless you can comment on the
strengths or weaknesses of these, it is hard to know how I can help
any more. If neither is adequate, please explain why and I'll try to
come up with another explanation. If you can already tell which you'd
like, let me know.

The TOP method I suggested really does something analagous to the
"record count" you mention. Your loop in code, producing successive
queries to export the data, would be much as you describe. Naming the
exported files would be in the general way you describe.

Please help participate in making a choice, then try programming
(CODING!) it and let us know if you need further help.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
The only weakness here is my skill level :) I think I might have come up
with a crude way to do this. I'm gonna see if I can play around with it and
get it to the point that an end-user can do it without too much trouble.
Thanks for the suggestions.


Tom Ellison said:
Dear Troy:

"I know....code, code, code. If it weren't for code, I'd be one
helluva programmer :)" You got a laugh here, guaranteed!

Well, the coding for the queries would be very much as I suggested.
Form what you have told me, you could replace my "SortKey1" with
"[Visit ID]". But you've still got some coding to do.

I really didn't see where you had considered the relative merits of
the two approaches I suggested. Unless you can comment on the
strengths or weaknesses of these, it is hard to know how I can help
any more. If neither is adequate, please explain why and I'll try to
come up with another explanation. If you can already tell which you'd
like, let me know.

The TOP method I suggested really does something analagous to the
"record count" you mention. Your loop in code, producing successive
queries to export the data, would be much as you describe. Naming the
exported files would be in the general way you describe.

Please help participate in making a choice, then try programming
(CODING!) it and let us know if you need further help.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

This is a list of data from hospital patient visits, so almost every field
is different, obviously with a small percentage of them as duplicates from
repeat visits. The "Visit ID" field would be unique. Only certain fields
would be duplicated, such as name, SSN, etc. I was hoping there would be an
easy way to do it, such as export the data until "record count=10000" is
reached, mark a field called "exported=1" as such for that 10,000 records,
then loop the process until all records are exported and the record count
reaches zero for the "exported=0" parameter. Of course, the exported file
names would have to be unique or incremented.

Again, I'm just saying how I would like to see it work, not that it can be
done this way. I know....code, code, code. If it weren't for code, I'd be
one helluva programmer :)




it
with for
me them
to
Click to expand...
Click to expand...
 
Back
Top