export data from multiple files into a single file

Hi all, hope I've posted this in the correct forum!

I have a folder full of csv files that I want to extract data from. It's foreign exchange data and is located in C:\Data\FOREX on my computer, each file name is for a currency pair(EURUSD.csv,AUDUSD.csv, etc). Data within the files is organised like this:

08/12/2011,1.0341,1.0361,1.0247,1.0353

What I want to be able to do is to extract data for a particular day from each file and have 1 excel file that contains the data for each day for all of the files in the folder, as well as the filename it came from, so that the end result looks like:

AUDUSD,08/12/2011,1.0341,1.0361,1.0247,1.0353
EURUSD,08/12/2011,1.4236,1.4291,1.4150,1.4246

Presently I open up a new worksheet, type in the currency pair in cell A1, then open up the corresponding file and copy and paste the data in cells A2 to A6, then do this for all 84 files in the folder. Obviously this isn't an enjoyable task and is something I'm looking at trying to automate.

Any help or tips on how to do this would be greatly appreciated.

Cheers

IF you would post a sample CSV file, it would be easier to help. This is needed so that we can see what fieldnames exist and what format your data has. You can obfuscate data if needed and trim the file size.

Explain how it would import by a date. Do you want to type it into the next row of column A or be prompted for it?

Hi Kenneth and Leith, thanks for replying

Have tried to attach a copy of 1 of the files, the forum doesn't want to let me attach a .csv file so I've added it as an excel file, hopefully you can get an idea of what they are like.

Given the choice, I'd prefer to be prompted for a date so I can pick and choose from the start.

Hopefully the file attached helps address any questions.

Cheers!

Hi leith,

thank you for your help, it's much appreciated

However, I'm having a little trouble getting it working - after opening the file you've attached, I clicked on the "Import Files" box on the worksheet, but it came up with an error. Rather than trying to explain it I just took a screen shot of the error:

Any idea what I've done wrong?

Since I already did this, I am posting this as an alternative method. This method uses ADO so you will need to set the reference as commented in the 2nd Sub. The input date can be in format of m/d/yyyy or m/d/yy. Since you did not use fieldnames, I had to create a schema.ini file for each csv file. It may have a function or two not used.

The code assumes that your csv files are in the workbook's path. It has lots of comments. Some are there to show some alternatives and some can just be deleted.

Please Login or Register  to view this content.