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

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?

-
not sure about the import- for the filtering you could maybe use something like: Select[{{"31/10/12 21:39", 13.1, " N"}, {"10/03/13", "", ""}, {"01/11/12 07:08", 9, " Y"}}, NumberQ[#[[2]]] &] –  Pinguin Dirk Mar 11 at 19:09

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
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"}}

-
Superb. Thank you very much for your advice. I'll have a play. –  TimGJ Mar 11 at 21:07
Curiously when I import the CSV file all text other than the date string is stripped from the comment lines and they appear as in my original post: presumably some difference in the formatting of our respective files. ON a related note can you recommend a reasonable guide somewhere online which explains the sort of magic used in the recipes above? (I'm a reformed perl programmer so am used to extremely cryptic syntax but have yet to find a decent single-source which gives a step-by-step explanation of how to build stuff like this). –  TimGJ Mar 11 at 21:19
@TimGJ Could you post an excerpt of your file (say 5 lines) that exhibits the behavior you describe? As to documentation:What about the official Mathematica documentation? To understand the above code you may need to read about patterns, string patterns,p‌​attern test and the function doc pages (highlight function name+F1) –  Sjoerd C. de Vries Mar 11 at 22:22
Sjoerd: Apologies. I've checked the file and it's actually LibreOffice that's mangling the CSV file (my Mathematica licence is on a Windows machine, normally I would edit the file using emacs under Linux). I've now taken the bull by the horns and deleted all the comments from the file as they weren't adding any value anyway. Thanks for your advice. –  TimGJ Mar 12 at 19:08