Sleeper: Updating values in worksheet from CSV Files

[Hamond]

Hi,

Sorry, this is a bit of a length one but I guess too much information is better than less!

I have an excel worksheet that I update regulary with latest values from downloaded CSV files. Right now, other than a couple of basic recorded macros to clean up the source data CSV files, most of this process is manual and I'l looking to automate it. I'm aware there is quite a bit of code posted on consolidating data but I've not seen anything that will work for me.

Here's my setup.

1) CSV Data Source Files: Have about 15 of these in the same folder. Each file has two columns, first is a date column and the second is values associated with that date. The length/no of rows of the data varies between files and not all files contain matching dates. All dates will have a value associated with it, there are no nulls/blanks.

2) Excel worksheet. This is where I currently update/consolidate the data from the source CSV files. Basicly, column A contains the dates and each successive column holds data relating to a particulary CSV files. When the latest data becomes avaliable I enter the entries in new rows for each variable. I do not erase or overwrite any of the old data, i.e I'm effectively building a timeseries. The actual data for each series starts in row 4. Where there is no value for a series/column for a given date, it is left blank.

The third row of the sheet contains codes relating to the source files. For example b3 = NBU1, this is the same as the corresponding file in the folder - the folder will have a file called NBU1.csv.

So basicly I'm looking for some code that will open each file in the folder, locate the relevant column in the spreadsheet based on the codes in row 3 and then copy over the latest data via I guess mapping the dates in the CSV files and to the dates in column A of the spreadsheet.

Hope someone can help.

Many Thanks,

Hamond

[Bob Phillips]

A couple of questions.

Are we looking at column pairs, one pair per file?

Is the file selection driven by the data in row 3?

If the data gets appended to that column pair, does it then get sorted, or are the dates always unique.

Got an example?

[Hamond]

Hi XLD,

Yes the file selection is driven by the data in row 3. Not sure what you mean by column pairs.

I've attached an example for a subste of the series to make things clearer. In the workbook sheet consolidated is the consolidation sheet. The other sheet shows the typical source file for a series, in this case for the the code DAAA. Of course orginally this source data sits as a CSV file in a folder with the other files.

As you can see, I only have one date field in the consolidation sheet, and this if you like is my "primary key". If data does not exist in the source file for a particular date then I leave that cell blank, if there is a data in the source file associated with a date that does not exist in the consolidation sheet then I ignore that value and do not copy it over.

Hope this clarifies things!

Hamond