Load an array, sort it, write it to a text file

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have a DB with 10 tables, various fields in each table, each field is text data type
DB also contains 10 queries(one for each table) that I export to a text file for each query. Currently I am opening each text file and cutting and pasting into one text file, because the end result must be sorted by the first two fields in each file and combined into on final file. Each line in the text files has a fixed length of 192 bytes.

I want to load each line into an array, sort it, and then write it out to the final file.
Is this the way to go, in this situation or is there a better way? Does anyone have any examples?

Thank you.
 
Any reason why you can't use a UNION query to combine all 10 queries'
results into one recordset, sort it, and then export it to a text file?

--

Ken Snell
<MS ACCESS MVP>

Chris said:
Hello,

I have a DB with 10 tables, various fields in each table, each field is text data type
DB also contains 10 queries(one for each table) that I export to a text
Click to expand...
file for each query. Currently I am opening each text file and cutting and
pasting into one text file, because the end result must be sorted by the
first two fields in each file and combined into on final file. Each line in
the text files has a fixed length of 192 bytes.
 
Honestly, I've never used a UNION query. Where can I find information about this?

Thnaks
 
Hello,

I have a DB with 10 tables, various fields in each table, each field is text data type
DB also contains 10 queries(one for each table) that I export to a text file for each query. Currently I am opening each text file and cutting and pasting into one text file, because the end result must be sorted by the first two fields in each file and combined into on final file. Each line in the text files has a fixed length of 192 bytes.

I want to load each line into an array, sort it, and then write it out to the final file.
Is this the way to go, in this situation or is there a better way? Does anyone have any examples?

Thank you.
Click to expand...

I agree with Ken. It's not necessary to write VBA code to do this -
Queries are tools designed to do exactly what you describe.

For a bit more detail, you can create a UNION query in the SQL window.
Each of the ten queries must have the same number of fields, in the
same order, with matching datatypes (but if you're creating a single
text file that's probably a given). The SQL would be something like

SELECT ThisField, ThatField, TheOtherField...
FROM FirstQuery
UNION ALL
SELECT AField, AnotherField, SomeField...
FROM SecondQuery
UNION ALL
<etc etc>
SELECT Field1, Field2, Field3, ...
FROM FinalQuery
ORDER BY 1, 3;

Note that the fieldnames don't need to match between the queries; the
names in the first SELECT clause will be used. If the *last* query has
an ORDER BY clause it will sort the records from the entire query; 1,
3 means the first and third fields will be used for sorting (i.e. it
will sort in order of ThisField and TheOtherField in my example).

You can then base an Export on this saved UNION query.
 
My read on this is that all ten tables contain similar information.
If true you need to redesign your tables so they all fit in one. This can be
done with an append query and probably one more field.

We would need to know what your tables contain to be sure.
 
A UNION query will not work in this case because each table has a different number of fields. Any other ideas?

Thanks
Chris
 
Pad with blank fields (or null fields) so that each SELECT statement has the
same number of fields. What were you going to do with the array if there
weren't the same number of columns for each row?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Chris said:
A UNION query will not work in this case because each table has a
Click to expand...
different number of fields. Any other ideas?
Thanks
Chris
Click to expand...
file for each query. Currently I am opening each text file and cutting and
pasting into one text file, because the end result must be sorted by the
first two fields in each file and combined into on final file. Each line in
the text files has a fixed length of 192 bytes.
 
Hi Doug

My original idea was that since each line in each of the ten text files are 192 bytes was to create a string array to accomadate the line length, sort the array, and then export it to one text file once it has been sorted. The problem is that the final file has to be in a set format, that each of the ten files takes care of seperately, and once it's all put together as one file it's fine. The final file is then sent to a third party partner that has strict guidelines about the format, so extra fields won't work in this case. Currently, I have to manually cut and paste each line one by one from each of the individual files and paste them into the final file just to get the thing to sort properly. If I can figure out how to load an array from the text files, apply the sort to the array, and then export it all to one text file, I think it would work.

Any Ideas?

Thanks
 
Are you saying that table 1 has 4 fields that total 192 bytes, table 2 has 5
fields that total 192 bytes, table 3 has 2 fields that total 192 bytes and
so on?

SELECT Field1 & Field2 & Field3 & Field4 AS OutputField FROM Table1
UNION
SELECT Field1 & Field2 & Field3 & Field4 & Field5 AS OutputField FROM Table2
UNION
SELECT Field1 & Field2 FROM AS OutputField Table3
ORDER BY 1

Or have I misunderstood?



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Chris said:
Hi Doug

My original idea was that since each line in each of the ten text files
Click to expand...
are 192 bytes was to create a string array to accomadate the line length,
sort the array, and then export it to one text file once it has been sorted.
The problem is that the final file has to be in a set format, that each of
the ten files takes care of seperately, and once it's all put together as
one file it's fine. The final file is then sent to a third party partner
that has strict guidelines about the format, so extra fields won't work in
this case. Currently, I have to manually cut and paste each line one by one
from each of the individual files and paste them into the final file just to
get the thing to sort properly. If I can figure out how to load an array
from the text files, apply the sort to the array, and then export it all to
one text file, I think it would work.
 
Doug,

That is correct, each table has a different number of fields, but the total record length from each row of each table is 192 bytes. I'll give the UNION QUERY a shot. I was under the impression that for a UNION QUERY each table had to have the same number of fields. I'll reply again if this does not work. Thank you very much for your help, I do appreciate it.
 
Okay, I tried the UNION QUERY, however that did not work because of the different number of columns in the various tables. It looks like, I'm back to square one.
 
If you look more closely at Douglas' e-mail he is concatnating the fields
together in each select statement of the union query, so there is only one
column in the resault. This should work, though it may or may not do what
you are looking for.

Sue Harsevoort

Chris said:
Okay, I tried the UNION QUERY, however that did not work because of the
Click to expand...
different number of columns in the various tables. It looks like, I'm back
to square one.total record length from each row of each table is 192 bytes. I'll give the
UNION QUERY a shot. I was under the impression that for a UNION QUERY each
table had to have the same number of fields. I'll reply again if this does
not work. Thank you very much for your help, I do appreciate it.
 
Back
Top