Table join with .csv file results in null fields after join

Question

Similar to questions already asked about .dbf files, I'm attempting to join .csv files to a .gdb or shapefile coverage in QGIS. The tables as added to QGIS prior to the join look fine, but after the join is complete, the amalgamated table has NULL values for all joined fields. The same happens with a variety of different tables, and regardless of whether I'm joining to a shapefile or .gdb file.

I'm running QGIS 2.0.1 on a OSX 10.8.5. The original table files are contained in .mdb format so I've purchased and used the MDB ACCDB Viewer to export them to .CSV The fact that they are fine when viewed separately leads me to believe the issue is with the join procedure, not the tables themselves.

Suggestions?

• KMT: Related to [link] (gis.stackexchange.com/questions/63804/…)
• SaultDon: Can you verify that the column in the csv is the same type as the column being joined by in the target shapefile/gdb? You can usually see the field types in the properties window for each layer by going to the Fields tab.
• KMT: Aha - so the shapefile has an integer field, while the all the table fields are string fields. Would the conversion from .mdb to .csv have lost the field types? Can I just change the field type in the properties dialog?
• Lauren: Be sure to check if you have exact matches. I had this issue even when field types matched. Turns out the issue was that I had leading white space in my strings. This was remedied by creating a new field by using the QGIS trim(oldField) functionality.
• Taras: Please, do not forget about "What should I do when someone answers my question?"

Answer 1

It's best if you make sure the two fields have the same data type.

The field type can't be changed for shapefiles. But it's easy to create a new empty text field and make that equal to the integer field.

You can use a field calculator expression that looks something like: tostring( "INT_FIELD" ) and simply replace INT_FIELD with the name of the actual integer field.

You can easily change a CSV layer attribute value from text <-> number too.

---

UPDATE

If you are getting null values, you could try keeping the join, but just close and re-open that attribute table to see if the joined values are there (or even close/re-open QGIS).

I've been able to successfully join a csv to a shapefile with a mixture of field types so string vs integer shouldn't be the issue.

---

UPDATE 2

Looks like in order to get rid of the null values, they have to actually have a matching record in the corresponding table that joins are being made from. But in the image below, you can see that some do have a matching record:

successful joins in yellow

• KMT: Ok, even when they're both string values, I'm having the problem of NULL values in the joined table. Other thoughts?
• KMT: No luck with closing/opening the attribute table or QGIS.
• SaultDon: @KMT Have any data you can share so anyone can test?
• KMT: Sure. Shapefile at dropbox.com/sh/jvbh2pafm2dreec/lr0GiFiq2t (using the "Delta Covers" file). Table at dropbox.com/sh/qw25uxmaoo6pgqh/s8EKrU9DfL (using "Covers_Survey" table). Also have a .gdb file (same as the shapefile) at dropbox.com/sh/8vdalc68jjd2c7i/iFpsm7MgAD. Join based on "Covlnk_text"(shapefile) and "Covlnktxt" (table) fields.
• SaultDon: @KMT I don't think converting field data types will help at all because the values still won't match. I would go back to the source data (the mdb) and compare values in the output csv to the mdb table(maybe they got messed up along the way somehow). LibreOffice Base can open an mdb too.

Answer 2

.csv files in QGIS by default import all data as strings. You need to create a .csvt text file that tells QGIS which columns should be specified as which data types.

A .csvt file contains only one line and the types for each column have to be quoted and comma separated, e.g.
"Integer","Real","String"

Quoted from: http://anitagraser.com/2011/03/07/how-to-specify-data-types-of-csv-columns-for-use-in-qgis/

Then your data will be of the same type, and the join should work.

• KMT: Thanks @Amos Budde, I'll try the .csvt route as well, but as it turns out, I tried a different shapefile and table, both with string fields, and still doesn't work - I get null values in the joined table. My data is shared above - any chance you can give it a try?
• KMT: just tried with a .csvt file and still no luck. Does it work for you?

Answer 3

I have met the same problem as you. I use ID field to join csv, the ID field type in shapefile is integer64,so i try to convert the ID field type to double using "Refactors fields" tool , and the ID field type in csv is set to numeric with 0 decimal places,then join successfully. I hope it helps.