copying multiple txt files to one txt file

[smith7255]

Hello,
I am hoping that someone can help me. I have an Access database that exports 5 queries to an ASCII txt file. I need to append them in a certain sequence and that will need to happen everytime the application is used. Let's say I have one.txt, two.txt, three.txt, four.txt and five.txt need to be copied into Allfiles.txt. I know that I can use c:\one.txt + c:\two.txt, etc. How can I get Access to run a bat file OR can I write a vbscript to do this? I don't generally write code so please give lots of detail. Thanks so much!

 

[CautionMP]

smith7255,

I don't generally write code so please give lots of detail.

Writting the routine is pretty easy, but before going down that path, have you considered using a Union Query to consolidate your five queries into a single query you can then export as a single ASCII txt file?

i.e.
[tt][tab]SELECT * FROM Query1
[tab]UNION SELECT * FROM Query2
[tab]UNION SELECT * FROM Query3
[tab]UNION SELECT * FROM Query4
[tab]UNION SELECT * FROM Query5;[/tt]

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[SkipVought]

I agree with CMP--way simpler!

Skip,

_[red][/red]

 

[smith7255]

The field lengths are not the same size, they don't contain the same type of data and they don't have a primary key to join them. I have a Record header, batch header, detail, a different batch header, more detail and then a trailer. Will the union query still work in a case like this?

 

[CautionMP]

smith7255,
A Union query doesn't 'join' data, it 'stacks' data, i.e. give me the results of query1, then the results of query2...

There is no join: the only critical factor is that all the queries have the number of output fields.

Q: Do all five of your queries have the same number of fields?

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[smith7255]

No, they don't have the same number of fields. They have anywhere from 2 to 6 fields.

 

[SkipVought]

Then what's the point of appending them ALL into a single file?

Skip,

_[red][/red]

 

[SkipVought]

Is this like a log file?

You could STILL do it. Like CMP stated, its STACKING the data.

Skip,

_[red][/red]

 

[smith7255]

When they are put together as one file it is a complete "batch" file that is imported into another application. It has Record01 with its information, Record05 for the money batch, Record10 for the detail money, Record06 for the stat batch, Record10 for the detail stats, Record99 for the trailer. See, each one containing different information but need to be one file to be imported into our GL system.

 

[SkipVought]

Why not do the whole thing in Access and export the COMPOSITE, allfiles.txt, at one time?

Skip,

_[red][/red]

 

[smith7255]

I would love to do the whole thing in Access, but I don't know how. Would I still be able to use Union query if they don't have the same number of fields?

 

[SkipVought]

Concatenate the fields in each query into ONE field, soemthing like this...

Select [Field1] & "," & [Field2]
From Table1
Union
Select [Field1] & "," & [Field2] & "," & [Field3]
From Table2

if not a comma then a space.

Skip,

_[red][/red]

 

[smith7255]

Ok, I will give it a shot. Thank you!

 

[smith7255]

I tried it out and it does join all the queries together but it does not keep them sorted. I need the detail records to stay under appropriate header. This mixes them up. In other words it should be sorted like this:
Record01 (Record Header)
Record05 (Money Batch Header)
Record10 (Detail Money)
Record06 (Stat Batch Header)
Record10 (Detail Stat)
Record99 (Record Trailer)

When I use the union query it sorts it like this:
Record01
Record05
Record06
Record10
Record99

Is there a way I can change the sort even though two of my query's first field begin with the number 10?

 

[SkipVought]

you know, I'm BLIND! I can't see what you see.

You have not told me enough about what you are doing to help.

I can't read your mind either.

Skip,

_[red][/red]

 

[PHV]

Try UNION [!]ALL[/!]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[smith7255]

Thanks, PH!! That did the trick.

 

[smith7255]

Oh well skip, blind or even more blind, you pointed me in the right direction. Thank you for your time and efforts.