Join text files together

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Basil - 15 Mar 2005 13:27 GMT

Hiya,

I need to import data from text files into a database... but it's a bit
complicated:

1. The data for each table is actually in more than one text file. Is there
a way that in VBA I can create a new text file that joins existing text files
together. I will need to join any text files ending in a particular phrase eg
'...JAN04.TXT'.

2. The data inside the text files is from a really really old system that
was meant for line printers - hence I will need to go through the new
document line by line and try and find a method to translate everything - it
will also probably contain non-printable characters (so maybe I'll have to go
through it character by character to replace the non-printable characters).
Am I thinking along the right lines, and will it take forever given that
there will be thousands and thousands of rows? Can anyone give me a headstart
in doing this?

Many thanks,

Basil

Reply to this Message

Allen Browne - 15 Mar 2005 14:10 GMT

Could you use the COPY command (at the Windows command prompt) to
concatenate the files? Something like this:
copy jan04.txt /b + jan11.txt /b + ... combined.txt

You could concatenate them in VBA with the Open command. By opening each
file in turn, and printing to the output file, it becomes a concatenation of
the previous ones. This would also allow you to strip out the non-printable
characters before printing the result to the output file.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> I need to import data from text files into a database... but it's a bit
> complicated:
[quoted text clipped - 23 lines]
>
> Basil

Reply to this Message

'69 Camaro - 15 Mar 2005 14:43 GMT

Hi, Basil.

Allen Browne has some excellent suggestions for appending the files together
into one. We've just started a series of technical articles on data cleanup,
so you may find some more tips that will help you take less than forever to
retrieve usable data. Please see the following Web page for a link to the
first article in the series, "How To Clean Up Data, Part 1: Web Pages And
Text File Data Sources":

http://www.Access.QBuilt.com/html/articles.html

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. (Only "Answers" have green
check-marks.) Remember that the best answers are often given to those who
have a history of rewarding the contributors who have taken the time to
answer questions correctly.

> Hiya,
>
[quoted text clipped - 18 lines]
>
> Basil

Reply to this Message