# Convert *.txt file

I would like to Import and Convert a *.txt file which looks like: https://docs.google.com/file/d/0Bw-308nJcF01Yl92RURCLUg4VzQ/edit?usp=sharing

and turn it into a Matrix which I can use to make computations which looks like this one:

 example = {{"DATE", "KEY", "C1", "C2", "C3"},
{{2013, 04, 01, 00, 00, 1}, 1, Null, 12.23,  Null},
{{2013, 04, 01, 00, 00, 2}, 2, Null, 2,      Null},
{{2013, 04, 01, 00, 00, 3}, 3, Null, 10.2,   Null}}

-
Is the first problem to Import the file ? Did you already succeed in importing it ? –  andre Apr 23 '13 at 15:40
@andre The Import itself is not the problem. What makes it difficult for me is that if I use StringSplit[] I loose the empty entries and also the date is separated. –  RMMA Apr 23 '13 at 16:42

First Import the file. Depending on the structure of your .txt file you can use the second argument ("Data" and "List" are useful choices). Remove the parts you don't need (in this case the ------) and split the resulting strings. An empty list shows up from all the dashed, you can simply remove this using /. {} -> Sequence[].

foo = (StringSplit /@ (StringReplace[#, "-" -> ""] & /@
Sequence[];


Now we build our sublists using DateList and ToExpression:

data = {DateList[{#1 <> "," <> #2, {"Day", "Month", "Year", "Hour", "Minute", "Second"}}],
ToExpression@#3, Null,
ToExpression@StringReplace[#4, "," -> "."], Null} & @@@ Rest@foo;


And finally assemble the result:

Prepend[data, First@foo]

(* {{"DATE", "KEY", "C1", "C2", "C3"},
{{2013, 4, 1, 0, 0, 1.}, 1, Null, 12.23, Null},
{{2013, 4, 1, 0, 0, 2.}, 2, Null, 2, Null},
{{2013, 4, 1, 0, 0, 3.}, 3, Null, 10.2, Null}} *)

-

First, import it:

q = Import["yourPath/example.txt"]


Then you can look to see what it consists of using

FullForm[q]


where you see it is a big string. So break it apart:

StringSplit[q]


and you get roughly the desired form.

-