Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

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)

"https://docs.google.com/spreadsheet/pub?key=0AqPfEWYQJhXSdGF4ck5pblVlaHB0bkE0MWhsOWZ6M2c&output=csv"

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.

EDIT (based on answers/comments):

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).

share|improve this question
    
So what is the output format you are seeking? –  Mike Honeychurch Apr 29 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 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 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 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 at 5:27

1 Answer 1

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.

share|improve this answer
    
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 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 at 16:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.