# Import CSV with “Numeric”->True and “DateStringFormat” problem

The CSV file linked below has 3 columns: "PT_ID" (char 4), "PHYS_ID" (char 2) and "VISIT_START_TIME" (varchar eg "0:00:15" hours minutes seconds)

Import["https://docs.google.com/spreadsheet/pub?key=\
0AqPfEWYQJhXSdGF4ck5pblVlaHB0bkE0MWhsOWZ6M2c&output=csv",
"DateStringFormat" -> {"Hour", ":", "Minute", ":", "Second"},
"Numeric" -> False]


Setting "Numeric" -> False will result in this output, so the hh:mm:ss is not matched

{Q001,01,0:12:15}

While setting "Numeric" -> True results in the following 2 seemingly unrelated problems:

{Q001,1,{2014,1,1}}

1. "PHYS_ID" is cast to Integer
2. the 3-element List contains Year, Month, Day rather than Hours, Minute, Second

Are there obvious solutions to both 1 and 2?

http://reference.wolfram.com/mathematica/ref/format/CSV.html isn't very usable.

If it is known a priori which elements (columns) should be handled (here column 3, rows >=2), then using "Numeric"->False but not using DateStringFormat, the data can be post-processed using:

MapAt[DateList[#][[4 ;;]] &, tmp, {2 ;;, 3}]

But the ideal is to have Import deal with it given the documented options in CSV.

DateStringFormat almost works. Used along with "Numeric"->True, it incorrectly returns the first 3 elements of DateList@"0:12:15" --> {2014, 1, 1, 0, 12, 15.} rather than the entire list. But if DateStringFormat is not used (and with "Numeric" -> True), then "0:12:15" is imported as-is (String).

-
So what is the output format you are seeking? –  Mike Honeychurch Apr 29 '14 at 22:10
My Internet connection isn't working properly right now; could you include the raw data in that remote file, please? –  Mr.Wizard Apr 30 '14 at 0:06
@Mr.Wizard, since this is an Import problem, I'm not sure how to include the raw data in an inline format. What do you suggest? –  alancalvitti Apr 30 '14 at 4:24
@MikeHoneychurch, the ideal state would be to not cast strings to integer (eg, preserve "01") but at the same time to parse the time, eg as {0,12,15} (hour, minute, second, as specified in "DateStringFormat") –  alancalvitti Apr 30 '14 at 4:27
I assumed from output=csv that it is a text-based CSV file. If not disregard that request; I'll try again later, or hopefully someone will have already solved your problem. –  Mr.Wizard Apr 30 '14 at 5:27

I think perhaps you've confused what DateStringFormat does. It provides a means to describe the input format - it is always then converted into DateList format unless other options of import are used that preclude conversion. It has nothing to do with the resulting imported "format".

That said, if I understand your question, what you want is to import, then massage the import:

data = Import["c:\\users\\rasher\\downloads\\importexample.csv", "Numeric" -> False];

massaged=MapAt[ToExpression[
StringReplace[#, h__ ~~ ":" ~~ m__ ~~ ":" ~~ s__ :>
"{" <> h <> "," <> m <> "," <> s <> "}"]] &, data, {2 ;;, 3}]


The result, in massaged, will be the first two fields, untouched in format, as strings, and the hour, minute, and seconds as the third field as a numeric list (which it appears is your desire). If you want the time as strings, just remove the "ToExpression" part of above and adjust format as desired.

-
I should have stated the intent was to avoid having to specify which parts (columns) to massage. Ideally, M should still interpret dates even with "Numeric"->False. –  alancalvitti Apr 30 '14 at 16:52
DateStringFormat almost works. Used along with "Numeric"->True, it incorrelctly returns the first 3 elements of DateList@"0:12:15" --> {2014, 1, 1, 0, 12, 15.} rather than the entire list. But if DateStringFormat is not used (and with "Numeric" -> True), then "0:12:15" is imported as-is (String). Isn't this a correct use of DateStringFormat? –  alancalvitti Apr 30 '14 at 16:59