0
$\begingroup$

I have many csv files in one folder and I have to import all of them into Mathematica. I need actually just two columns and all the rows except for the first one so I got rid of another 6 columns and the first row using Cases and Rest commands. It actually works pretty well (only date format is wrong because my csv is in european date format day.month.year hour:minute and Mathematica says it is ambigious and writes in american format, see pic) but only when I import one csv. If I try to import more it doesn´t work. Could anyone please help me to import all csvs from the folder and tell me how to tell Mathematica to write it in european date format?

My code for multiple csvs:

  allfiles = 
  FileNames[All, 
   "N:\\ DataCacheScada \\ IIS \\ vocko01 \\ Distribucni_kabely \\"];
    cableD = Cases[
   Rest[Import[#, "Table", "FieldSeparators" -> ";"] & /@ 
     allfiles], {x1_, x2_, x3_, x4_, x5_, x6_, 
    x7_}] /. {{x1_, x2_, x3_, x4_, x5_, x6_, x7_} -> {DateObject[x6], 
     x7}}

Wong date format for one cvs only

You can download two of my csvs here: https://gofile.io/?c=rxLrwn

Thank you for your advices!

$\endgroup$
  • $\begingroup$ To get rid of messages about ambigious format one can make stringreplace: {DateObject@StringReplace[#1,x : DigitCharacter .. ~~ "." ~~ y : DigitCharacter .. ~~ "." ~~ z : DigitCharacter .. ~~ " " ~~ rest__ :> z <> "/" <> y <> "/" <> x <> " " <> rest],#2}&@@@Import["file.csv","Table","FieldSeparators"->";"][[2;;,{-2,-1}]]. This is for one file, no errors. Then you can map to all files. $\endgroup$ – Alx Jul 31 at 15:01
  • $\begingroup$ Welcome to Mathematica.SE! I suggest the following: 1) As you receive help, try to give it too, by answering questions in your area of expertise. 2) Take the tour! 3) When you see good questions and answers, vote them up by clicking the gray triangles, because the credibility of the system is based on the reputation gained by users sharing their knowledge. Also, please remember to accept the answer, if any, that solves your problem, by clicking the checkmark sign! $\endgroup$ – Michael E2 Jul 31 at 15:13
  • $\begingroup$ To map to all files change @@@Import["file.csv","Table","FieldSeparators"->";"][[2;;,{-2,-1}]] to @@@#&/@(Import[#,"Table","FieldSeparators"->";"][[2;;,{-2,-1}]]&/@allfiles) $\endgroup$ – Alx Jul 31 at 15:14
2
$\begingroup$

Well, to summarize my comments:

{DateObject@StringReplace[#1,x:DigitCharacter..~~"."~~y:DigitCharacter..~~"."~~z:DigitCharacter..~~" "~~rest__:>z<>"/"<>y<>"/"<>x<>" "<>rest],#2}&@@@#&/@
(Import[#,"Table","FieldSeparators"->";"][[2;;,{-2,-1}]]&/@allfiles)

So, if you know that you need only last two columns you just take only them during Import, no need for complicated Cases structure. StringReplace makes exchange date fields to the form "YYYY/MM/DD HH:MM:SS", then DateObject happily interprets these parts as properly formatted inputs.

$\endgroup$
  • $\begingroup$ Thank you, it works exactly how I wanted! I appreciate it! $\endgroup$ – Vocis Aug 2 at 12:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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