How to merge multiple comma delimited .dat file matrices into one matrix using MATLAB?-CodePudding

Before I start, I want to thank you for taking the time to help me with this issue.

I have a folder filled with hundreds dat time series files. Equally, each file has hundreds of rows of time series data. As an example, here are the four header rows and five data rows for two files:

"TOA5","69927","CR1000","69927","CR1000.Std.31.03","CPU:QikIsland_Met_2017_v1.6.CR1","57632","CBAY_MET_1MIN"
"TIMESTAMP","RECORD","BattV_Avg","AvgBattV","PTemp_C_Avg","Kdn_Avg","Kup_Avg","Ldn_Avg","Lup_Avg","CNR4TC_Avg","CNR4TK_Avg","LdnCo_Avg","LupCo_Avg","WS_ms_Avg","WS_ms_Max","WS_ms_Min","WS_ms_Std","WS_ms_S_WVT","WindDir_D1_WVT","WindDir_SD1_WVT","AirTC_A_Avg","RH_A_Avg","AirTC_B_Avg","RH_B_Avg","BP_Avg","xco2_cp_Avg","Pumps_ON","Licors_ON","EC150_ON","HeatVent_ON"
"TS","RN","Volts","","Deg C","W/m^2","W/m^2","W/m^2","W/m^2","Deg C","K","W/m^2","W/m^2","meters/second","meters/second","meters/second","meters/second","meters/second","Deg","Deg","Deg C","%","Deg C","","kPa","","","","",""
"","","Avg","Smp","Avg","Avg","Avg","Avg","Avg","Avg","Avg","Avg","Avg","Avg","Max","Min","Std","WVc","WVc","WVc","Avg","Avg","Avg","Avg","Avg","Avg","Smp","Smp","Smp","Smp"
"2017-05-04 14:54:00",2713,13.44,13.39,-10.94,652.7,439.7,-93.8,1.293,-10.91,262.2,174.3,269.4,2.404,2.94,2.019,0.239,2.404,3.589,2.461,-50,0,-15.06,72.6,101.8,407.8,1,1,1,0
"2017-05-04 14:55:00",2714,13.45,13.38,-10.97,650.8,440,-93.7,0.37,-10.9,262.3,174.5,268.6,2.51,2.92,2.274,0.184,2.51,0.197,5.514,-50,0,-15.03,72.69,101.8,407.8,1,1,1,0
"2017-05-04 14:56:00",2715,13.46,13.4,-10.84,648.9,439.8,-94.3,1.346,-10.76,262.4,174.4,270.1,2.073,2.332,1.862,0.133,2.073,353.7,3.669,-50,0,-15,72.68,101.8,407.6,1,1,1,0
"2017-05-04 14:57:00",2716,13.46,13.41,-10.71,648,441.7,-94.9,1.531,-10.67,262.5,174.3,270.7,2.002,2.685,1.686,0.289,2.002,350.9,3.847,-50,0,-14.94,72.67,101.8,407.4,1,1,1,0
"2017-05-04 14:58:00",2717,13.46,13.42,-10.59,647.2,440.4,-94.9,0.396,-10.66,262.5,174.3,269.6,2.497,2.744,2.195,0.15,2.497,352.6,1.437,-50,0,-14.93,72.61,101.8,407.7,1,1,1,0

"TOA5","69927","CR1000","69927","CR1000.Std.31.03","CPU:QikIsland_Met_2017_v1.6.CR1","57632","CBAY_MET_1MIN"
"TIMESTAMP","RECORD","BattV_Avg","AvgBattV","PTemp_C_Avg","Kdn_Avg","Kup_Avg","Ldn_Avg","Lup_Avg","CNR4TC_Avg","CNR4TK_Avg","LdnCo_Avg","LupCo_Avg","WS_ms_Avg","WS_ms_Max","WS_ms_Min","WS_ms_Std","WS_ms_S_WVT","WindDir_D1_WVT","WindDir_SD1_WVT","AirTC_A_Avg","RH_A_Avg","AirTC_B_Avg","RH_B_Avg","BP_Avg","xco2_cp_Avg","Pumps_ON","Licors_ON","EC150_ON","HeatVent_ON"
"TS","RN","Volts","","Deg C","W/m^2","W/m^2","W/m^2","W/m^2","Deg C","K","W/m^2","W/m^2","meters/second","meters/second","meters/second","meters/second","meters/second","Deg","Deg","Deg C","%","Deg C","","kPa","","","","",""
"","","Avg","Smp","Avg","Avg","Avg","Avg","Avg","Avg","Avg","Avg","Avg","Avg","Max","Min","Std","WVc","WVc","WVc","Avg","Avg","Avg","Avg","Avg","Avg","Smp","Smp","Smp","Smp"
"2017-05-05 00:01:00",3260,12.06,12.06,-20.6,-3.329,-0.676,-62.68,-4.122,-20.95,252.2,166.7,225.2,2.375,2.489,2.176,0.103,2.375,171.6,14.43,-50,0,-20.8,83.2,102.1,410.7,1,1,1,0
"2017-05-05 00:02:00",3261,12.06,12.06,-20.63,-3.854,-1.329,-62.4,-4.017,-21.05,252.1,166.6,225,2.602,2.764,2.352,0.132,2.602,175.2,8.6,-50,0,-21.36,82.7,102.1,410.9,1,1,1,0
"2017-05-05 00:03:00",3262,12.04,12.06,-20.66,-3.529,-0.878,-61.38,-3.567,-21.06,252.1,167.6,225.4,2.479,2.666,2.274,0.093,2.479,151.4,4.677,-50,0,-21.23,83.1,102.1,410.7,1,1,1,0
"2017-05-05 00:04:00",3263,12.06,12.06,-20.67,-3.179,-0.293,-60.76,-3.435,-21.08,252.1,168.1,225.5,2.773,3.018,2.254,0.2,2.773,142.2,1.946,-50,0,-20.98,83.4,102.1,410.6,1,1,1,0
"2017-05-05 00:05:00",3264,12.05,12.06,-20.7,-2.928,0.158,-60.67,-3.171,-21.11,252,168.1,225.6,3.01,3.195,2.94,0.068,3.01,142.2,2.803,-50,0,-20.83,83.5,102.1,410.5,1,1,1,0

Now, I'm looking for a MATLAB solution to:

Read all the files in my folder
Merge all files into one comma-delimited matrix while deleting the header rows for subsequent files
Export the file to Excel

Thank you again. It means a lot.

CodePudding user response：

I think this is probably close to what you want:

ds = tabularTextDatastore(folderContainingYourDataFiles);
ds.NumHeaderLines=4;
while hasdata(ds)
    tbl = read(ds);
    writetable(tbl,'mySpreadsheet.xls',WriteMode='append');
end

I'm not understanding the mapping between the header content and the 30 columns of tabular data in the non-header portion. But, if you do, then you can specify the property "VariableNames" on the datastore ds.