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.

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.


"Ian" wrote:

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.

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.

Ian wrote:

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.

--

Dave Peterson

Dave,

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

Cheers,
Ian.

"Dave Peterson" wrote:

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.

Ian wrote:

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.

--

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.

Ian wrote:

Dave,

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

Cheers,
Ian.

"Dave Peterson" wrote:

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.

Ian wrote:

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.

--

Dave Peterson


--

Dave Peterson

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" wrote:

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.

Ian wrote:

Dave,

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

Cheers,
Ian.

"Dave Peterson" wrote:

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.

Ian wrote:

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.

--

Dave Peterson


--

Dave Peterson

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

Ian wrote:

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" wrote:

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.

Ian wrote:

Dave,

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

Cheers,
Ian.

"Dave Peterson" wrote:

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.

Ian wrote:

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.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson