0

I have more than 100 .dbf files with common 3 column names. I want to merge all with their second row values into one xls file. Also, eager to add new column with file names (consisting of POINTIDs).

Thanks in advance.

I have tried to some basic excel methods but I didn't

This is the one of dbf file

enter image description here

All of my dbf files

enter image description here

jrcalabrese
  • 2,184
  • 3
  • 10
  • 30
tasoglu
  • 3
  • 3
  • You can likely use `foreign::read.dbf` to read each file. There are portions of [list-of-frames](https://stackoverflow.com/a/24376207/3358227) that discusses how to read multiple files into a `list`, and from there you can take the next step. It is not clear in your question if by "merge" you mean `rbind` or the merge/join operations discussed here: https://stackoverflow.com/q/1299871/3358272, https://stackoverflow.com/q/5706437/3358272. – r2evans Mar 08 '23 at 13:38
  • Because DBF files allegedly do not always follow the documented "standards" (ahem) for the format, [`read.dbf`](https://stat.ethz.ch/R-manual/R-devel/library/foreign/html/read.dbf.html) self-admits that it is likely to not be able to read some DBF files. Because of this, while I can simply _suggest_ use of that function, I have no assurance that it will work for you, and (more importantly) I have no way to actually test that (and image of a file is somewhat informative but insufficient here). Is there a way that you can make one such dbf file available to us? – r2evans Mar 08 '23 at 14:10

1 Answers1

0
library(foreign)
library(data.table)

source_files = list.files("H:/dbf", pattern = "*.dbf", full.names = T, recursive = F)

merged <- rbindlist(
  lapply(source_files, function(file) {
    x <- read.dbf(file, as.is = FALSE)
    setDT(x)[, POINTID := gsub("POINTID_(\\d+).dbf", "\\1", basename(file))]
  }), fill = T
)

merged

#    VALUE COUNT              AREA POINTID
# 1:     1 75527 234657.9565700000   24153
# 2:    23   855 124657.3365700000   24154

# simply save as a csv which you can of course open in Excell
fwrite(merged, "results.csv")

# or write as Excell instead
library(openxlsx)
write.xlsx(merged, "results.xlsx", overwrite = TRUE)
Merijn van Tilborg
  • 5,452
  • 1
  • 7
  • 22