1

This question already has an answer here:

shape fileI tried mapping a table that does not have a spatial geometric component (CSV file) into a shapefile in QGIS with a common ID column. But I noticed that most of the data which is on the CSV file does not show when I open the attribute table in the shapefile.

My CSV file has 728 rows and my shapefile has only 160 rows

After I join these two tables with a common ID column, only 160 rows are being shown on the shapefile attribute table.

Does anyone know how to combine all the data in the CSV to the shapefile?

marked as duplicate by underdark qgis May 20 '17 at 13:03

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 1
    The shapefile format actually has a limit of 100 fields due to the dBase III+ file specification. Many applications ignore this, but then run into the hard limit of 255 fields (the field count is stored in a single byte). dBase also has a transfer buffer limit of 4000 bytes, which 700+ fields might also exceed. – Vince Apr 12 '17 at 18:15
  • 1
    Are you saying that your CSV file has 728 columns, and your shape file has 160 columns, or do you mean records (rows, or lines)? That seems to be a rather large number of columns for any data set, as is the 160 columns for the shape file. If possible, open the CSV file in Excel, delete unnecessary or blank columns, then save the file with another name as a CSV file. I would also look at the shape file and delete any unnecessary or blank columns and save that as a different file as well. I hope this helps! – jbgramm Apr 12 '17 at 19:16
  • Sounds like your join isn't working at all. Are you sure you've chosen the right columns for the join? Are you sure they are exact matches? There's not spaces in the join item names? We can't really help much here without a copy of your data, or at the very least some screenshots of what you've done. – Spacedman Apr 13 '17 at 7:31
  • @jbgramm CSV = 728 rows 8 Columns, Shape file = 160 rows 7 Columns – Kaveenda Apr 14 '17 at 15:40
  • @Spacedman hi i just added a screen shot of my shape file. And yes I chose the Poll_ID column for the join as it is common and exact in my csv file – Kaveenda Apr 14 '17 at 15:59
3

Joins only add columns to existing features. If the shapefile only has 160 features, a join cannot increase the number of features!

Use the virtual layer feature instead. It offers real SQL/database-like joins. For an example see my recent post: Small multiples for OD flow maps using virtual layers

enter image description here

1

Forgive me, this is going to be long winded but hopefully it will provide you with the answer to your question.

You only need to pay attention to the top two files in the Layers Panel.

enter image description here

The MB2D_Parcels_0531 is a shape file with geometries (Polygons) The Surface is an imported CSV table with data that corresponds to the shape file.

When I browse the Parcels table, you can see it has 1055 records

enter image description here

I am now going to join the Surface file to the Parcels file.

enter image description here

I have chosen the proper fields to make the join with, and I will press the Apply button.

enter image description here

My join was successful, and now I will browse the Parcels table just out of curiosity.

enter image description here

As you can see, I still have 1055 records, but my Surface table has been added to the data of the Parcel table.

I am now going to select data from the Parcel table that contains Surface data as well.enter image description here

Note that there are 138 records selected. I also know I have duplicated Parcel ID numbers in my Parcel file due to parcels being split, my Surface file has 80 records.

From here, I am going to view the selected parcels in map window.

enter image description here

The selected parcels are the magenta polygons.

I am now going to unselect all of the polygons, and break the join between the Parcel file, and the Surface file. I have also changed the file order so the Surface table is above the Parcel file in the Layers Panel (Just a personal habit for clarity I suppose.)

enter image description here

I am now going to browse the Surface table. You will see there are 80 records contained in the table.

enter image description here

From here, I am going to join the Surface table to the Parcel table.

enter image description here

After picking the appropriate fields in both tables for the join, I will press on the Apply button

enter image description here

I can now browse the Surface table with the join to the Parcels table applied. You will see there are still only 80 records, but the Parcel data has been added to the Surface table.

enter image description here

I am now going to select some records where there is corresponding data in both tables after the join.

enter image description here

From here, I am going to view the map window, you will notice that there are no Parcel polygons that are selected due to the fact that there are no geometries to select from the Surface file.

enter image description here

Hopefully this is of some help.

I did not want to get into a whole left join, right join type of explanation, but to simplify it.

If you wish to join tabular data to a file that contains geometries, you highlight the geometry file, get the properties, and perform the joining of the tabular data there.

If you wish to join the tabular data of a file that contains contains geometries to a table that is strictly tabular data, highlight the table, get the properties, perform the join there knowing you will not be able to select the geometries that may be associated with the join.

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