How do I read a CSV file and remove comments from it

Question

I have very recently started using Mathematica 9.0 Home Edition and am trying to understand it as best I can so please be tolerant ;). I have a file of data (sugar.csv) which contains blood sugar readings in the form

dd/mm/yy hh:mm, x[.y], ['Y'|'N']

e.g.

10/03/13 19:04, 4, N

or

28/11/12 03:41, 6.2, Y

which I am reading in to a nested list using Import. Some records in the file are comments beginning with a # symbol e.g.

# Records added 01/04/12

Such comments seem to be interpreted by the import process as just date entries (see second item below)

{"31/10/12 21:39", 13.1, " N"}, {"10/03/13", "", ""}, {"01/11/12 07:08", 9, " Y"},

Is there any way I can automatically strip these lines out of the input as part of the import process? (For that matter, is Import the best function to use to process CSV input?) Alternatively is there any way to apply a filter to the list to remove all elements whose second item is not a number?

Answer 1

When I use Import to import a test file consisting of the following 4 lines

10/03/13 19:04, 4, N
28/11/12 03:41, 6.2, Y
# Records added 01/04/12
10/03/11 19:04, 4, N

using "CSV" as the specified format

Import["C:\\Users\\Sjoerd\\Desktop\\test.dat", "CSV"]

I get

{{"10/03/13 19:04", 4, " N"}, {"28/11/12 03:41", 6.2, " Y"}, {"# Records added 01/04/12"}, {"10/03/11 19:04", 4, " N"}}

(note that strings don't show their quotes in Mathematica's default output format)

Records starting with a "#" can be easily removed using DeleteCases:

data2 = DeleteCases[data, _?(StringMatchQ[#[[1]], "#" ~~ ___] &)]

{{"10/03/13 19:04", 4, " N"}, {"28/11/12 03:41", 6.2," Y"}, {"10/03/11 19:04", 4, " N"}}

If you want to remove all lines containing "#" (instead of starting with "#") you could changes the above pattern to ___~~"#" ~~ ___

By the way, note that Mathematica can convert the date/time stamp to a more usable format using DateList:

MapAt[DateList[{#, {"Day", "Month", "YearShort", "Hour", "Minute"}}] &, data2, {All, 1}]

{{{2013, 3, 10, 19, 4, 0.}, 4, " N"}, {{2012, 11, 28, 3, 41, 0.}, 6.2," Y"}, {{2011, 3, 10, 19, 4, 0.}, 4, " N"}}