Search code examples
rdataframegeospatialread.csv

Import datetime in one column of txt-files

How do I import the datetime column correctly? date+time in one column as as DATE

I have got a txt-file with columns: seriesnumber, time and temperature.

I tried:

test.c <- read.csv("110.txt", header = F,skip = 26, sep = "",  dec = ",", col.names = "Seriennummer","Zeit","Temperatur°C")

However, this always gives me:

enter image description here

This is how the data looks like in the editor:

enter image description here

Data:

readLines("8.txt",n=10)

 [1] "Seriennummer                      Zeit\tTemperatur°C"
 [2] "         1           16.03.2023 15:17:33      26,8"  
 [3] "         2           16.03.2023 15:18:33        27"  
 [4] "         3           16.03.2023 15:19:33      26,8"  
 [5] "         4           16.03.2023 15:20:33      26,3"  
 [6] "         5           16.03.2023 15:21:33        26"  
 [7] "         6           16.03.2023 15:22:33      25,5"  
 [8] "         7           16.03.2023 15:23:33      25,3"  
 [9] "         8           16.03.2023 15:24:33      25,1"  
[10] "         9           16.03.2023 15:25:33      24,9"
Solution

  • read.fwf (read fixed-width fields) should work well, but you would have to count characters in the line to determine the appropriate widths= argument (it's too hard to tell from the image you posted).

    I don't think base R converts to date-time formats automatically, but readr::read_fwf() (which is similar to read.fwf but a little more flexible) might. It's possible that it will correctly "guess... based on the positions of empty columns" (the default), you could try it ...

    It looks like this works (guessing doesn't work because it wants to split date and time again ... I used a column-number-aware text editor to figure out the appropriate start/end positions for each field)

    library(readr)
read_fwf("fwf.txt", 
         ## doesn't seem to handle header, and TAB in
         ## header might be problematic anyway ...
         skip = 1,
         ## feel free to use German names instead ...
         fwf_cols(n = c(9,10),
                  datetime = c(21,40),
                  temp = c(46,50)),
         locale = locale(decimal_mark = ","))