Merging files
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. |
Merging files
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. |
Merging files
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 |
Merging files
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 |
Merging files
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 |
Merging files
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 |
Merging files
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 |
All times are GMT +1. The time now is 05:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com