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.

I have files that look like this:

8853,548600 39,021824   
8143,632600 37,200790   
7815,964200 35,926544   
7106,337000 34,761822   
6724,260400 33,491154   
6178,100200 32,254318

I want to import it and then change the decimal point from , to . and convert it so that it has the the right head (so I can manipulate it like a number).

My attempt that doesn't work:

data = Apply[StringReplace[#, {"," -> "."}] &, 
  Import[file, "Data"], {2}]
share|improve this question

4 Answers 4

up vote 10 down vote accepted

For several reasons I recommend that you do not use VLC's method. First, it is going to be slower than interpreting the data during import, and second it creates the possibility of unintentionally running code via ToExpression.

The most direct and robust method is to specify "NumberPoint" in Import:

Import["file.txt", "Table", "NumberPoint" -> ","]
{{8853.5486, 39.021824}, 
 {8143.6326, 37.20079}, 
 {7815.9642, 35.926544}, 
 {7106.337,  34.761822}, 
 {6724.2604, 33.491154}, 
 {6178.1002, 32.254318}}

This is documented in the Options section of the individual Import/Export format pages, e.g. "Table".

For faster importing, at the risk of crashing Mathematica on irregular data, you may make use of the undocumented internal function described here which is actually the subroutine used by Import without all the slow dressing used to auto-detect formats.

table = ReadList["file.txt", Word, RecordLists -> True];

System`Convert`TableDump`ParseTable[table, {{{}, {}}, {"-", "+"}, ","}, False]
{{8853.5486, 39.021824}, 
 {8143.6326, 37.20079}, 
 {7815.9642, 35.926544}, 
 {7106.337,  34.761822}, 
 {6724.2604, 33.491154}, 
 {6178.1002, 32.254318}}
share|improve this answer
    
Good points, never thought about the possibility that some code could be unintentionally executed. I'm going to delete my answer, so nobody will be tempted to use that approach. –  VLC Nov 9 '12 at 10:36
    
@VLC Please don't; it's a good reference and it illustrates a way to make the method that the OP attempted work. I just think in this case it is not practical. –  Mr.Wizard Nov 10 '12 at 21:25

This should solve your problem:

ToExpression[StringReplace[#, "," -> "."]] & /@ 
  Import[file, "Table"]
share|improve this answer
    
@Althalos You might reconsider your choice of accepted answer (see Mr.Wizard's answer). –  VLC Nov 9 '12 at 11:35

This will do the job on Linux, or if you have Linux utils installed on Windows.

data= ReadList["!sed 's/,/\./g' /tmp/data.txt", {"Number", "Number"}]
share|improve this answer

A general solution for importing .csv files is the use of StringReplace. First import the data

dataRaw =   Import["......KlimaLoggPro4.csv"];
dataRaw // Length

18133

E.g. the following two lines come from a climate logger. The second line is the data and is of course available several thousand times. As you can see, it is completely gobbled.

{{"Timestamp\";\"TI\";\"RHI\";\"DEWI\";\"T1\";\"RH1\";\"DEW1\";\"T2\";
\"RH2\";\"DEW2\";\"T3\";\"RH3\";\"DEW3\";\"T4\";\"RH4\";\"DEW4\";\"T5
\";\"RH5\";\"DEW5\";\"T6\";\"RH6\";\"DEW6\";\"T7\";\"RH7\";\"DEW7\";\"
T8\";\"RH8\";\"DEW8"}, {"2014-01-31 21:15:00;\"20,2\";\"49\";\"9,2\";
\"21,3\";\"48\";\"9,9\";\"---\";\"---\";\"---\";\"---\";\"---\";\"---
\";\"---\";\"---\";\"---\";\"---\";\"---\";\"---\";\"---\";\"---\";\"---
\";\"---\";\"---\";\"---\";\"---\";\"---\";\"---\";"}}

The following list must be executed in this order and cleans up the data in each list of data to conform with Mathematica.

strReplacList = {"," -> ".", ";" -> ",", "\"" -> "", "---" -> "Missing[]"};

I always extract the headings in a separate list.

lstHeadings =  StringSplit[StringReplace[dataRaw[[1, 1]], strReplacList], ","]

Note the use of StringReplace and StringSplit

data = Transpose[(ToExpression @ MapAt[DateList, First@StringSplit[StringReplace[#, strReplacList], ","], {1}]) & /@ dataRaw[[2 ;; All]]];
Dimensions[data]

{28, 18132}

As you can see, we now have a list we can work with.

data[[All, 5000]]

{{2014, 3, 24, 23, 0, 0.}, 18.6, 56, 9.7, 16.5, 64, 9.7, 13.8, 61, 6.4, 20.4, 52, 10.2, 18.6, 55, 9.4, 3.4, 87, 1.4, 2., 93, 1., Missing[], Missing[], Missing[], Missing[], Missing[], Missing[]}

I later use this as TemporalData.

share|improve this answer

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.