Convert *.txt file

Question

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}}

Answer 1

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[#, "-" -> ""] & /@ 
       Import["D:\\Downloads\\example.txt", "List"])) /. {} -> 
    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}} *)

Answer 2

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.