0

I already asked in previous question QGIS table join issue, how to join shape file data and CSV data which contain multiple SAME ID for one object?

Shape file contains columns: object id, area, height etc

CSV contains columns: object id, materials as brick, metal, concrete etc

So first I was joining them by JOIN option, but the numbers did not match as the software only recognises one value of the field and overwrites it to all other same IDs.

I was offered solution Project>Properties>Relations and I used instructions on: https://docs.qgis.org/testing/en/docs/user_manual/working_with_vector/attribute_table.html#id40. But I have over 84000 rows and don't have time to go through all of them and make connections. Further this option does not seem to have all the information I need for example in the attribute table. I would ideally like to be able to do this one-to-many join such that I could have overview of each material (concrete, steel, brick etc) for each field (I did not figured that out with relation).

Join table would be ideal but as stated the numbers do not match so it is issue with representation. Also it is important to have all materials for each field as they will be intersected with different layers and the correct amounts are needed for further calculations.

Improve this question
3
  • Did u really mean 84000 fields (=columns) ?
    – Snaileater
    Commented Aug 16, 2019 at 6:31
  • No, sorry I meant rows.
    – Julija M
    Commented Aug 16, 2019 at 14:01
  • I have about 15 columns
    – Julija M
    Commented Aug 16, 2019 at 14:01

1 Answer 1

Reset to default
-1

One way to get there is to open the .dbf (d-base file) and the .csv as a data frame in R, then merge the tables and overwrite the original .dbf with the joined data. 

#dependency
install.packages("foreign")
library(foreign) 

#read in d-base table
df.1 <- read.dbf('**pathtotable1.dbf**')

#read in .csv file
df.2 <- read.csv('**pathtotable2.csv**')

#merge tables based on commmon ID field
m = merge(df.1, df.2, by="**YOURIDFIELD**", all.x = FALSE,  all.y = FALSE)

#overwrite original .csv file
write.dbf(m, "**pathtotable1.dbf**', factor2char = TRUE, max_nchar = 254)
Improve this answer
15
  • 2
    dBase-III has a documented limit of 100 fields and a hard limit of 255 fields. It is not possible to write 84,000+ fields to a shapefile. There's also a 4000 byte transfer buffer limit on the dBase specification, so even if that many fields were permitted, they couldn't be wider than 0.38 bits each.
    – Vince
    Commented Aug 15, 2019 at 20:45
  • @vince Given the clarifications in the comments above (IE that this data has only 15 columns, not 84000) is this now a viable solution?
    – csk
    Commented Aug 16, 2019 at 19:47
  • @Cory what do you mean dbf file? I am not sure about d-base file, I am mac user.
    – Julija M
    Commented Aug 16, 2019 at 20:05
  • @csk Big difference on 84k fields vs. 84k rows, but using Excel on a shapefile's dBase file is still a recipe for disaster, especially in the hands of a novice -- too many opportunities to corrupt/reorder the row count and completely lose association between features and attributes. I didn't downvote, but I can't upvote either.
    – Vince
    Commented Aug 16, 2019 at 21:17
  • @JulijaM When you create a shapefile in ESRI's shapefile format (.shp), there are some other files that are created alongside it. Namely, an index file (.shx) and a d-base table (.dbf). The table can be edited according to your desired additions in a more manipulable format (i.e. .csv), then rewritten as a .dbf table so that the main shapefile (.shp) will reflect the changes.
    – Kartograaf
    Commented Aug 16, 2019 at 22:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.