importing multiple csv into 1 table

Hi there

I have a bunch of csv files, and each file is data from 1 day. The csv files follow this structure:

# Wed Oct 5 2011 12:00:03 EDT 2011
# URL:http://abc.com/go_here_for this data/get_data?some_param1=true&Date=02-Sep-2011&some_param2=123
#header1,header2,header3,
data1,data2,data3
data4,data5,data6

I need to import all this data into 1 table in an xlsx file which looks like this:

date,header1,header2,header3
date_csv1,data1,data2,data3
date_csv1,data4,data5,data6
date_csv2,data7,data8,data9

etc.

Although I've programmed in college, I'm pretty new at programming macros. Could somebody point me to what are some of the functions that would be useful to accomplish this? The pseudo-code I've come up with is:

csvfiles=list of csv files
while csvfiles
open current_csvfile
data_date=date from current_csvfile.row3
add data_date to col1 of every row starting row4 to eof
copy current_csvfile.row4:current_csvfile.eof
paste current_csvfile.row4:current_csvfile.eof to table.starting.col2
csvfiles=remove_first_file
end

I've uploaded 3 sample files. Had to rename them to .txt or it wouldn't let me upload, perhaps I'm doing something wrongly. So ...

marketsummary1.txt and marketsummary2.txt are the .csv files that I'll get from the server. As you can see, the first line is the date where the summary was generated, and the second line has the date of the data embedded in a URL.

I would like to ADD these summaries into a master xls file like aggregate_marketsummary.txt ... which is created by copying the data rows over and adding a header from 1 of the csv's and a date column (every market summary file has info on the same markets).

I've tried writing a macro that would find embedded date from the URL and insert it into the first column ... but everything is hard coded and hardly elegant. I'm not sure whats the best way to add the data rows from each marketsummary?.txt to the aggregate file ... Any help would be appreciated.

snb/JBeaucaire

I think snb made an interesting suggestion. It sounds like it would be easier to
1) "copy *.csv monthly.csv"
2) Work on the monthly.csv file, which would look like:

# Wed Oct 05 11:16:58 EDT 2011
# URL:http://gohere.com/to/get/this/report...celSize=100000
Pool Name, Quantity, Quantity(%), Average, this, that, everything, else
Carolinas,49494,100,112,33,4,11,23
Florida,343431,100,333,111,222,555,331
Michigan,343411,100,332,514,2156,12,34
New York,67845,100,7625,23,452,76,4573
Ohio,3415623,100,3251,4516,8745,75434,22235

# Mon Oct 03 12:11:23 EDT 2011
# URL:http://gohere.com/to/get/this/report...celSize=100000
Pool Name, Quantity, Quantity(%), Average, this, that, everything, else
Carolinas,49494,100,786,768,8687,786,34
Florida,343431,100,675,78,222,4564,74
Michigan,343411,100,78,786,657,5464,43
New York,67845,100,756,86,828,537,5689
Ohio,3415623,100,75,67,456,786,389

Then, I'd have to:

1) use the rows with " URL" as the beginning of a new "day"
2) add the date column
3) delete 4 rows: the 2 rows before " URL", the " URL" row and the row after that.

What do you think?

It does sound that way.

Can I get some direction as to what functions, methods etc I might need to:

1) find the "# URL" row?
2) use it as a reference point to work on the rows before and after it?

Cool, thanks for all the help. Let me try to understand the code ...

Sorry, was hijacked to do another project so I didn't have time to work on this side project of mine.

JBeaucaire, I think I must be doing something wrong here. I tried the code and it didn't work as intended. Can we start again ...

Input file: marketsummary_all.txt (a csv file)
Output file: aggregate_marketsummary.txt

When I tried to run your code on marketsummary_all.txt (after commenting out the joining/importing bits), it only read the first date and put it in every row of col A. Any ideas?