Web Development

General discussion


VBA - Combining 2 text datasource files

By cnwoods ·
I have 2 textual comma delimited files I need to query regularly, with some overlap in data and almost identical fields (different names on 2 of them). These are for orders filled locally and for orders filled for locals (filled anywhere).

So far I've been doing it with 2 access queries - a straightforward one for the first then a non-matched query for the second, but I don't know how to combine both these queries into a single report (grouped by the filter criteria and including both datasources under each heading). It ends up in a word report, so I'm wondering if I'd be better off biting the bullet and creating a vba routine to import the two files, except there are two things I need to know first:
1. How do I get an import or merge to access data from two text files, and
2. I don't know how I'd de-dupe the result (there will sometimes be legitimate duplicate entries where a single client has received multiple orders, so if a duplicate from the second file has already been removed, the next entry should be allowed).

Am I better off creating a query combining the two tables, generating a report and setting word up to pull it in using VBA; Doing it in Word in the first place via a VBA modification of the merge direct from the text files; or a combination using access to combine the two files and word to automatically filter/group it into the weekly templates I use. This already accesses an excel spreadsheet to pull in other stats, in case that impacts.

Although I'm pretty comfortable writing VBA routines in xcel, I'm relatively new to VBA in Access & Word. Answers assuming low-level knowledge would be appreciated.

Thanks, Crystal

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by jjonas In reply to VBA - Combining 2 text da ...

Some questions to help answer your question :
Can both text files be read into the same table ?

If so, can you define a combination of fields in the table that would make an entry unique (a primary key). My thinking is that if you could do that then you could make a primary key for the table and have dupes be rejected that way.

Good luck

Collapse -

by cnwoods In reply to

Unfortunately not. While most of the fields overlap, there are about 4 which don't, since we have greater detail on the local clients than on those serviced elsewhere. Also, the overlap wouldn't be on a one-to-one. There may be one true overlap, then 3 non-true, or any other combination.

Actually, the overlap is going to be tricky enough that I'll probably end up manually editing it, but I don't know how to merge a word document from two sources. Any ideas?

Collapse -

by cnwoods In reply to VBA - Combining 2 text da ...

Point value changed by question poster.

Collapse -

by Choppit In reply to VBA - Combining 2 text da ...

I'm not sure I've grasped the full requirement, but heres my thoughts anyway....

Connect to the two text files as linked tables and use the SQL UNION command to create a combined (super) table.


In VB you could parse each text file line by line and construct SQL INSERT statements to your database via DAO.


Have you considered using Excel VLOOKUP or Pivot Tables to do this for you?

Collapse -

by cnwoods In reply to

Have played around with both these since your posting, but can't seem to get them to work what I need. I'm not too flash hot on sql, I must admit, and then the de-duplication stuck me even further.

Sorry, still no joy, but I do appreciate your leads... (Delay involved death of old computer, resurrection of new one and attempts to salvage data in meantime)

Collapse -

by cnwoods In reply to VBA - Combining 2 text da ...

This question was closed by the author

Related Discussions

Related Forums