10

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}]
C. E.
  • 70,533
  • 6
  • 140
  • 264

4 Answers4

14

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}}
Community
  • 1
Mr.Wizard
  • 271,378
  • 34
  • 587
  • 1,371
  • 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 09 '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
5

This should solve your problem:

ToExpression[StringReplace[#, "," -> "."]] & /@ 
  Import[file, "Table"]
VLC
  • 9,818
  • 1
  • 31
  • 60
  • @Althalos You might reconsider your choice of accepted answer (see Mr.Wizard's answer). – VLC Nov 09 '12 at 11:35
2

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"}]
image_doctor
  • 10,234
  • 23
  • 40
0

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.

Ernst Stelzer
  • 2,055
  • 12
  • 24