Merging files

[Guest]

I have a series of around 500 .csv files, stored in one defined directory,
which I would like to merge into one Excel spreadsheet. All the .csv files
have data in the same format but very in the number of rows of data they
contain, varying between one and ten rows of data.

Is possible to write a piece of code that will open each .csv file, in the
directory, in turn, determine the number of rows present, copy that number of
rows of data and then paste|append those rows into a master Excel worksheet?

Thanks for any pointers,
Ian.

 

[Guest]

I've been looking at Leith Ross's solution to a similar issue posted
yesterday which goes some way to answering my problem. However his solution
throws up formatting issues for me as two of the fields I wish to import are
date and time.

Look forward to any ideas,

Ian.

 

[Dave Peterson]

You could open each of the 500 files and merge them together, but I think I'd
use an old DOS command to merge the data before it hits excel.

Shell to DOS
(windows start button|run
type this and hit enter:
cmd
(or
Command
for win98)

Go to that folder with the 500 .csv files.

Copy *.csv All.Txt
(don't use All.CSV)
now rename All.Txt to All.CSV

and import that all.csv file.

 

[Guest]

Dave,

Excellent tip, worked a treat and just what I wanted without the programming
headache, thank goodness for DOS!

Cheers,
Ian.

 

[Dave Peterson]

You could even set up a small .bat file and run that if you need to do it again
(and again and again)...

And if you get industrious, you could call that .bat file within a VBA macro.

 

[Guest]

DAve,

Already done the .bat file as I will be repeating again and again! Not
thought about the macro though, is it fairly straightforward?

Cheers,
Ian.

 

[Dave Peterson]

Take a look at Shell in VBA's help to run that .bat file.

DAve,

Already done the .bat file as I will be repeating again and again! Not
thought about the macro though, is it fairly straightforward?

Cheers,
Ian.