Help with getting some times from a data set

27 views (last 30 days)
Travis
Travis on 4 Dec 2024 at 11:36
Commented: Travis on 4 Dec 2024 at 14:20
Hi all, I have a data set which contains some times. I cant just normally import it as a Datetime table or vector since the time formats through out the table are not consistent. Additionally there are also \N values to show there is not time for that specific row (Although I have a way to sort through these).
My final goal from the data is to add times together within a specific indexing range. But since its imported as categorical data I have no clue what to do. The attached is the data im working with. If this is something that is easier to manually fix in the data please let me know.
EDIT: Apologies I attached the wrong version of the file. The new verstion should be the .csv
  2 Comments
Cris LaPierre
Cris LaPierre on 4 Dec 2024 at 13:09
Edited: Cris LaPierre on 4 Dec 2024 at 13:30
Ran in:
This file is not a csv file.
T1 = readtable('time.csv')
Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.
T1 = 1629x44 table
PK____________b__h_____________Content_Types__xml______________ x____DO97_______8_O_c__n_____E_________B_______________________ Var3 Var4 Var5 Var6 Var7 Var8 Var9 Var10 Var11 Var12 Var13 Var14 Var15 Var16 Var17 Var18 Var19 Var20 Var21 Var22 Var23 Var24 Var25 Var26 Var27 Var28 Var29 Var30 Var31 Var32 Var33 Var34 Var35 Var36 Var37 Var38 Var39 Var40 Var41 Var42 Var43 Var44 _________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________ _________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________ _______________________________________________________________________________________________ ________________________________________________________________________________________________________________________________________________________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ {'?□9LìÒ□□ÈsbgÙ®|Èl!õù□USh9i°b□r:"y_dlÀóD□¿□ý|-N□ÈR"4□ø2ÏGÇ% õ□Z´4ñË□yÄ7->ëÈðÉ□□□¨Þ□ ÿÿ□ PK□□□ □ ! [)˨□□ □ xl/workbook.xml¬Umo□:□þ>iÿ□ñ□b□ □5□ÂÛn¥¶ªÒ¬Ý¤H□□Nð-àÌ□&Uµÿ¾i»LW¹Ý¢ÄÆöáñsÎyÎÉé§mYh□TÔ□Wc□□ ]£UÊ3V­Æú□ybxºVKRe¤à□□ëO´Ö?M>~8ÝpñpÏù□□ U=Ös)×¾iÖiNKR□ð5­àdÉEI$' } {'Åʬ×□□¬Î)□ea¹fIX¥¾8□□/□' } {'¥□O□□V²□□´ □è×9[×=Z□□□W□ñЬ□□□k□¸g□□O-¨®□©□¶ª¸ ÷□¸½Å□¶□ðuá□□□V□□□□\U²Tð□/å->@□□é□ÿ121~□□ía□□C²MA□□Êá□□pßÉÊÝc¹/`□ý1□□iµZñ!xïDsöÜ'} {'}rºd□½é¤«□õú□□*S□®□¤□qÆ$ÍÆú□□|Cßl□f□4¬□SËÃÖH7'{9_->-£KÒ□r□BîáÁ□Y□□□cZH**"iÈ+->:Üùõ§□k±Ã□□µ□ýÞ0A¡°@_à+□$õÉ}}Ed®5¢□ë¡¿øR1U»□=-p-µo□□E|ùù3¶□»□­□$□□W+VQ* ¢□->Ö®□ÿ□¦R=□×7õâ□□ÉaÍü□!□TEÇ□ðt.tÏ¿□'} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {'<□~/Ö+)4x>□Î!a×ä□Ò□"ÉvÕ}□ùñî□q□Xn4õ□A□"Ã□F®□Ä□kDAà□□Và□ÓÑ□ðB¸~ÊI#ó□$□æX·!ÿ□G□dÛ□`ä7' } {'{¹ÿ□í>□□□□ú³□ÊSÕün□ÝÔ/âQKm{˪□o □□Üyê×□V%°iOoY&seb[û½□([å@□#¥4Õb□µ±þl»(□□□□ز!□->□□/□bÃM□ÏFa<Â6j)□¯8µ}□¸µ³Vµµq­z/□□®f□^x□¾ºC□e¸M_ÿZJ□□jAM­£®PèV□×rr' } {0x0 char } {0x0 char } {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {'3È□□=¸}:D#Û@ñÀ1lod□□=°□Ð□¬Ø□ÆQ□8*Aê□Âÿ□ݲ­□¿ÿ□R' } {'s"ä\□ô□D>£Ë□Ô ¥Î!àù□làx□□ cbØ□#\Ûp¢dà□q□ÆNòBV¹¿|g¯òÌömJd□u¬J¸]ûjLv»ûÍe·±ËÓ□ªóg□ÊÌîíÿ2¼□ï□z¤qrs¤axy1¿8Òö<□ßÝ&Ç□O/□hz¼ýt6□~□Ç_û+Ìß□Ôl□®ÆV¦f/□ÉO ÿÿ□ PK□□□ □ ! □>□□ó º□ □□xl/_rels/workbook.xml.rels ¢□□(  □ ¬RMKÄ0□½□þ□0w□v□□Ùt/"ìUëÉ´)Û&!3~ôß□*º]XÖK/□o□yïÍÇv÷5□â□□õÁ+¨□□□z□lï;□oÍóÍbí­□□G□□□ìêë«í□□□s□¹>□È'} {'□□8æø(%□□£¦"Dô¹Ò□4jÎ0u2jsÐ□ÊMYÞË´ä□ú□Sì­□´·· □)fåÿ¹CÛö□□□y□Ñó□->I<' } {0x0 char } {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {'y ÑèÔ!+øÁEò¼üfMyÎkÁ£ú□å□«K□ª5=|□t □ÈG□□)□så¢□»UïátBûÊ)¿Ûò' } {'ËôïfäÉÇÕß ÿÿ□ PK□□□ □ ! 4é-w□U□ □□$ □ xl/worksheets/sheet1.xml□□[□□0□Fß+õ?X~□□□\PÈ*l6ê¾UÛË»c□`□ÛÔvnªúß;□%»R^¢□@□□9g□□ó§□jÉ□¬□F□4□#J@□SI½-è¯□ë`J□ó\W¼5□' } {0x0 char } {0x0 char } {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {'z□G□□_¿Ì□Æî\□à->□´+hã}□3æD□□»Ðt ñMm¬â□□í□¹Î□¯□□TË□(□3Å¥¦□Bn□a□º□□VFì□h□□Xh¹Çþ]#;w¥)Nq»Ûw□0ªCÄF¶Ò□□(%Jä¯[m' } {'ß´8÷)N¹ '□W□÷èª□ÖïLJ' } {0x0 char } {0x0 char } {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {'k□©}□dvéù~ü□□1.n¤ûù□ÂÄ)³p□ý□¾£□ϵ□g7Vò□□}□6¾Áúßeó½¬' } {0x0 char } {0x0 char } {0x0 char } {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {'ú7Z□Q□O£`ò¼□□i□fÁì%+□é$^□/Ër4Ëâ□t1¯$□p?□±P□t□çËd□%□e□ù□¡ß□□îCM<ßü□□□□ôÄ□ô□Ý□³ë7¾âR□T7lè©\xy□ghÛ□□□¦üÏàÁ□□ìføX_më!Ôß-© æûÖ¿□ã7□ÛÆ£6' } {0x0 char } {0x0 char } {0x0 char } {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {'□5¤"¯Î+p□c□êpÔsÿ□ ÿÿ ÿÿ□□íÎôÌQ­O□å?fúÓv□"!Á□D!Òþ□bÃÙïU5□{ìè­§ëºõ□□Zééñ¸ºúãêU□øÛÿùË_þë_ÿô_□úã□þóÿþ¿□øÏ□þ]ùÝ?üí?þô׿é?ý^ÿù□JÿÓ□□ÿïÿû¯□ùÛ□ÿò×ÿúçß½¶6~÷Ç?üÙ´ÿ"□þ_□Óÿýß□|ýá□þû□□ø§?ë□ÔÔO{□´gbë□µW·^ι□³^ÿ□□ûÇúÛ□ÑÈg4o}l}ßÏÛßñÛMwÒt÷¦□­Ìöºýµßnz□¦í□ÿ÷□˾□£Ü□.¿Ýô$MÏwÓÇö*íþD~»é□4-ñÏû□tõ íI¼jï$íI¼j¯¼P@H½l□□Ø=Æ□gXP□□zÙG□REêe□(□□ÔË□Q □©□-¢¸)R/[DáR□ñRPÀ□zÙG□2%□3□Å□©W}¬(fL½l□%¦DÌT□3¦^ö□ÅL5õ7{□½·×øùëaö¬(□¤öLtnc□2>□¢úN?íØúH$Í□□Jjël□ÛÑê:mV□[R{ãs□ý¸5□LR*'} {0x0 char } {0x0 char } {0x0 char } {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {'3©½ñÓz¾□¦4□q¦þy÷¢9□□¸v□¸`¾ÓPÄ□ú§□=□÷±□ß=âFÐ"□¸vÏR3h□E□æÖËQ¡¡ø2õj□i(¨L½l□ERKd©□ÂÇÔ«>v□3¦^¶□b¦'²TG1cêe□QÌôD□êl□□□Ùu□3¦^~k□3ÝÔ·L:_Ç~Îúù□fÒ□âHj□âÛÖÚ□' } {'>Q□õ+□mçyOG¯`□ï( ¤¶Î*K¿Î{□□æ □Å□Ô¾â□Û~Ü[□□;□□□Ô□IËÖJ□G□□2©¯IEßï©9ø□□' } {0x0 char } {0x0 char } {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {'8©­ñ¡□ËãQD(öÆ=ö¢_□mKÜc/ØB□(öLý□ÍÑ#Eq6□ùj H3õjÄ□(¼L½jq¢□2õ²E□H3□¯&□$S/û□Âg&òÕD1cêe□QÌÌD¾□l□/1Ç□(fL½üÖ(f¦©¿9uÎ×y□¶¿Þÿj□S'□#©ß9µÖL□ÜQHIí□¿ö`÷ÄRuGÑ%µ□üÛ<□' } {'µ£Ø□Ú□ÄØÎ□X□î(̤öÎ6í□□ÛÄ(Èö÷Îù8¶Ú£o□bl□ïR□sëí¾­□Ì□v□n¦þ□Gpî±³□òD¼í(ÞL½□à□Å□©W-□( L½l□EÑ□ÈQ□□#S/û□□çHä¨□□□©□}Dñs$rÔ□□ÆÔË>¢□92§Kìx)□3□□□S¯¾õ□bÆÕ□<Z¶cÖCË□Ï_□óè□âHêw□ݵ"[□' } {0x0 char } {0x0 char } {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {'□(¤¤~çѺk□pûûíTr¢è□Ú×□e;{°Éw¢à□Ú□űí=Hv'□-©¯ä¹×û□k° ;Q□□úûÂEýEqv>â' } {'È '' } {0x0 char } {0x0 char } {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {'4S□{□¬mOv□û□´`□¡£sv□□9Î}±ó\□¯□□òb'º&_·ÉÎt_□CÝ□;Õ5ùº□ì\÷□9Ø}¡ *&_÷□□í¾2□»/vºkòu?Ùùî+□²' } {0x0 char } {0x0 char } {0x0 char } {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {'¤"\þIZ]#s-?»©íUþí□£x-' } {'¶$□§­×ì÷□ZÄM@pBòwâ□□□H\Å SInÍ□s«gbÑR□□ Í¿□YíÛ□□n□Q□¤Éw*«¶-|_d□Ï□□□ÿ9_ZqÿöÜ □2A:|í¹·­□w□*HA□Ò□□{□Û¶□÷óæ i□□)Hï3¡Éà□□¡/' } {0x0 char } {0x0 char } {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {'Ã/\¾□□*Kq&_·É□.Ã`□Ç*òD¤É×ýdaf□ƺM□[â.□m²è28cÝO□â□ÉX·ÉâÈP□e□□©(.ÿîo□Û□üAWÏpYV□□ C□ä□#æÖ□9"□q□tQ$÷Õ□□%G4h5□d□{Ò¬ÛÔzïû□6ϲ□□□o¾i□3±□*FX□çýÎp}ßö½¯×ª□Á□&¿Ö□ã¼?□ðçd□)TÃ□ï[->□7□G¡□W□½EI□¡□□□?Mê->'^□Æm□'¸□l->□□1Àkñd7□ô§0zÃåËaÉ□□ÒÑÌ*□□□Åäë~²8Ë@□Ź□|*6ùº□' } {'¢□ñX·ÉbJàF¢M□â□îXö□Q□Ååß□WË□□Ùã□Ç□□r□□ºìp□÷e\4□1|£\ü□□-5□' } {0x0 char } {0x0 char } {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {'□³Q.hC-□õ¾]□-Z□à 1' } {'ùµ□□á0>X¸IîYS·y^©GÌ&□□8¼ù²½Úý*L□Ö□Ñ □äÂz5KÙÃ□□Å □□Ïkê±Þëï_Øc□□â;®´©3ïÛ4"Øó-□õ0¹7¯YJ°N6x□<aɽA]' } {0x0 char } {0x0 char } {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {'□÷iOôF3î£<Á□°Ï' } {'ÕeØ□Âà□□/□Q□:ȳͬæ□ RL¾î'□²□□R□ëȧd□¯ûÉb□e□□ô(.ÿ¦º½□÷□Ë□V)□úpù·ïQt1ò£□üÛhÀÐ□F|¸|ý□Yb□ö□øåXt□üH´ÉÒÙ□FX□|□□¯□'Kb□æ£0èÃåË~2ì£d¸□ÝsE£j□ü(□ýpùú»³' } {'□¡?' } {0x0 char } {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char} {0x0 char}
It appears to actually be an xlsx files whose extension has been changed to csv.
T2 = readtable('time.xlsx')
T2 = 26519x1 table
time ___________________________ {'2.41967592592592581e-02'} {'5.478' } {'8.163' } {'17.181' } {'18.014' } {'\N' } {'\N' } {'\N' } {'\N' } {'\N' } {'\N' } {'\N' } {'\N' } {'\N' } {'\N' } {'\N' }
That causes a problem, because some of the values in the spreadsheet are formatted dates, and others are numbers. The problem is that, when they are formatted as dates, the actual value is captured as a serial date, and the formatting makes it look like a time. For example, the first value displays as 34:50.6 in Excel, but the actual cell value is 0.0241967592592593
If you use Excel to export the file as a csv, you can obtain the same results you see in Excel
T3 = readtable('mytime.csv')
T3 = 26519x1 table
time ___________ {'34:50.6'} {'5.478' } {'8.163' } {'17.181' } {'18.014' } {'\N' } {'\N' } {'\N' } {'\N' } {'\N' } {'\N' } {'\N' } {'\N' } {'\N' } {'\N' } {'\N' }
There are challenges working with either format since the number formatting is inconsistent.
Travis
Travis on 4 Dec 2024 at 14:19
Apologies I have corrected the data file now.

Sign in to comment.

Sign in to answer this question.

Accepted Answer

Cris LaPierre
Cris LaPierre on 4 Dec 2024 at 14:16
Ran in:
I found it easiest to work with is the real csv file.
T = readtable('mytime.csv','TextType','string');
% extract the values that are not formatted as times.
% Convert to seconds (duration data type)
d = seconds(str2double(T.time));
% clean up formatting of values formatted as times (mm:ss.S)
T.time = erase(T.time,{'+','-'});
% Convert time to duration
T.time = duration(T.time,'InputFormat','mm:ss.SSS','Format','mm:ss.SSS');
% add seconds back into table
idx = ~isnan(d);
T.time(idx) = d(idx)
T = 26519x1 table
time _________ 34:50.600 00:05.478 00:08.163 00:17.181 00:18.014 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN

More Answers (0)

Categories

Find more on Spreadsheets in Help Center and File Exchange

Tags

Products


Release

R2024b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!