Updating cells with new data from CSV Files

[LucasLondon]

Hi,

Sorry, this is a bit of a lengthy 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.

There is only one date field in the consolidation sheet (Col A,) 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 in the column for that indicator blank, if there is data in the source file associated with a date that does not exist in the consolidation sheet (Col A) then I ignore that value and do not copy it over.

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,

Lucas

[kadghar]

Hi there.

Call me lazy, but the way I would do it is importing into a sheet all the info from all the CSV, one after other, i. e.

Column A: Dates
Column B: Info
Column C: FileName

And then use the magic of Excel's Pivot Tables.

Could that help?

[Killer42]

Subscribing .

[LucasLondon]

Hi Gents,

Thanks for your suggestions. The pivot table is a good idea but the only problem is that some of the source files don't contain the entire history but only recent data, hence I'm storing the previous/historical data in the summary sheet and adding the new data to it.

Lucas

[poolboi]

Guys,
sorry to interrupt the discussion but since it's almost similar to my problem
just like to know how do u import yr data from CSV file into yr excel sheet?
i seriously have no idea how that can be done
thanks

[Killer42]

To get a CSV file into Excel, just open it.

[poolboi]

hm...
opps should be more specific
i need to use perl program to actually help me input datas from csv file to excel file
without all those commas

[romcab]

Hi guys,

I created a simple apps which automate excel file using C#.net. It's quite similar to your problem so I paste here a link where I based my idea and some of my code snippet.

http://www.codeproject .com/KB/cs/Simple_Excel_Au tomation.aspx


public bool OpenExcel()
{
bool rtn = true;

try
{
m_wb = m_app.Workbooks .Open(m_filenam e, 0, false, 5, System.Reflecti on.Missing.Valu e,
System.Reflecti on.Missing.Valu e, false, System.Reflecti on.Missing.Valu e,
System.Reflecti on.Missing.Valu e, true, false, System.Reflecti on.Missing.Valu e,
false, false, false);

m_app.Visible = true;
}
catch (Exception ex)
{
Console.WriteLi ne("Error: " + ex.Message);
rtn = false;
}

return rtn;
}

public void CloseExcel()
{
m_wb.Save();
m_app.Visible = false;
}

//set sheet to open
public void SetExcelSheet()
{
m_sh = m_wb.Sheets;
}

//Get all reference number on a given sheet
public void GetRefData(int sheet)
{

m_ws = (Worksheet)m_sh[sheet];
for (int i = 5; ; i++)
{
string row = "A" + i + ":" + "A" + i;
Range cell = m_ws.get_Range( row, Type.Missing);

if (cell.Value2 != null)
{
if (sheet == 1)
{
m_array1.Add(ce ll.Value2.ToStr ing());
}
else
{
m_array3.Add(ce ll.Value2.ToStr ing());
}
}
else
{
break;
}
}
}

[Killer42]

hm...
opps should be more specific
i need to use perl program to actually help me input datas from csv file to excel file
without all those commas

No matter what language you drive it from, Excel should have no problem importing a CSV file.

However, this is the Visual Basic forum. You should ask in the Perl forum if you need help with that language.

[poolboi]

opps sorrie
no wonder the language is so weird
i didn't see the visual basic heading on top
sorrie sorrie