reading a csv file with a multiline header and alphanemeric dataset

I have a csv file (e.g. filename.csv) with a multiline, irregular header with a fixed length (17 lines). following this, there are columns of data, that I wish to make numeric array. columns may contain empty cells, and non numeric content, which I would want to replace with zeros in my resulting vector. here is the csv file, with a portion of the header:

"Legend"
"M","Missing"
"E","Estimated"
"NA","Not Available"
"**","Partner data that is not subject to review by the National Climate Archives"

"Date/Time","Year","Month","Day","Time","Data Quality","Temp (°C)","Temp Flag","Dew Point Temp (°C)","Dew Point Temp Flag","Rel Hum (%)","Rel Hum Flag","Wind Dir (10s deg)","Wind Dir Flag","Wind Spd (km/h)","Wind Spd Flag","Visibility (km)","Visibility Flag","Stn Press (kPa)","Stn Press Flag","Hmdx","Hmdx Flag","Wind Chill","Wind Chill Flag","Weather"
"2014-04-01 00:00","2014","04","01","00:00","**","2.4","","1.8","","74","","8","","17","","12.9","","101.16","","","","","","NA"
"2014-04-01 01:00","2014","04","01","01:00","**","2.5","","0.9","","78","","7","","21","","16.1","","101.15","","","","","","NA"
"2014-04-01 02:00","2014","04","01","02:00","**","2.1","","0.3","","84","","7","","18","","16.1","","101.09","","","","","","NA"
"2014-04-01 03:00","2014","04","01","03:00","**","2.3","","0.2","","84","","7","","15","","16.1","","101.06","","","","","","NA"
"2014-04-01 04:00","2014","04","01","04:00","**","3.0","","0.9","","76","","7","","13","","16.1","","101.03","","","","","","NA"
"2014-04-01 05:00","2014","04","01","05:00","**","2.8","","0.7","","78","","8","","17","","16.1","","100.96","","","","","","NA"
"2014-04-01 06:00","2014","04","01","06:00","**","2.7","","0.4","","80","","8","","18","","16.1","","100.93","","","","","","NA"
 etc.

The data I would like to access is wind direction and wind speed. This should result in a direction vector of:

8
7
7
7
7
8
8

and a speed vector of:

17
21
18
15
13
17
18

I would ideally like two vector, one with direction and the other with speed. If I could obtain another vector with the time (just the hour value) that would be great as well.

Any help is always appreciated!

[Image Analyst]

I helped by fixing your formatting above. You could too once you view this : http://www.mathworks.com/matlabcentral/answers/13205-tutorial-how-to-format-your-question-with-markup

[John]

Super! Thanks

Answer 1

[Sara]

[~,~,a] = xlsread('file.xlsx','M18:M53');
for i = 1:length(a)
   if(~isnumeric(a{i}))
      a{i}=0;
   elseif(isnan(a{i}))
      a{i}=0;
   end
end
a = cell2mat(a);

[John]

Sorry sara, I am going to try and reword the question, and show part of the csv file I wish to access

[Sara]

Can you attach part of the file itself? It's kind of difficult to read.

[John]

There it is, and thank you again for all your help!

[Sara]

With the code I've posted above you can extract all the column you want, if you know in which cell they start and end. You can put it in a function and call it for different columns and obtain all the different variables. For instance, wind direction will be in the interval M18:M350 in the file posted. Have you tried the code?

[John]

I tried running it with the file eng-hourly-04012014-04302014.csv instead of file.xlsx, and using M18:M350 but an error statement occurred saying the following:

??? Error: File:csvetest2.m Line: 1 Column 3
Expression or statement is incorrect--possibly unbalanced (, {, or [.

Perhaps this has to do with the csv file format being incompatible with xlsread?

Or perhaps I have missed something?

[Sara]

Post the code you're using so we can see exactly what you're doing (Line 1 will do). From the error you get, it seems that you're just missing a parenthesis in the matlab command. About the cvs, you can try saving the file as xls in case.

[John]

[~,~,a] = xlsread('enghourly0401201404302014.xlsx','M18:M350');
for i = 1:length(a)
   if(~isnumeric(a{i}))
      a{i}=0;
   elseif(isnan(a{i}))
      a{i}=0;
   end
end
a = cell2mat(a);
a

I thought that there may be an error in the filename so I changed it to enghourly0401201404302014.xlsx

[Sara]

The file is a cvs, so you need to use:

[~,~,a] = xlsread('enghourly0401201404302014.cvs','M18:M350');

[John]

I saved the file as a csv and xlsx in the same directory and ran the code using both files individually. the same error message appeared both times.

[Sara]

I don't get any error running the code for the cvs file you've posted. Just to make sure, is the matlab script in the same folder of the cvs file?

[John]

I still had trouble running it, with the same error message, and the files were in the same directory. I ended up using just the syntax:

speed = xlsread('Wind.csv', M18:M350');

This just gave me the numerical values I needed. Thanks for the help Sara! :)