Joining CSV to shapefile with same Name field results in NULL fields

Question

I am having some issues with using the join feature between my shapefile polygons and my CSV data (originally a xlsx file), when I join the layers together using the common field between them (name) it results in NULL fields. The joining name fields are both text (string), I have tried converting them to dbf files instead of CSV and had some partial success (some files work, others do not) I have come to the conclusion there must be some sort of invisible characters in my data that is messing it up.

How can I check for this?

Other than that I have no ideas, and have combed through past questions to no avail.

I would upload files but it seems that is not an option here.

using the name field to join two datasets

the result (null vales?)

• PolyGeo: What past questions have you looked at? Providing a few links to them will let potential answerers save time by not having to re-trace your steps in looking for them.
• Stu Smith: I've had similar issues that were solved by searching for and deleting double (and triple, and so forth) blanks, both leading and trailing.
• ahmadhanb: Try to use Trim Function in Excel: support.office.com/en-us/article/… to remove any white spaces in the Name field.

Answer 1

I would run trim("COLUMNNAME") against both layers using the Field Calculator to either update the existing column, or save the treated values to a new column, and attempt the join again with the newly created temporary layer copies.

Trim will drop all leading/trailing white space characters, but will do nothing for errant extra spaces between words, if present. You'd need to try something like regexp_replace("COLUMNNAME",'\s+',' '), which should normalize any multilple whitespace characters to a single space.

You could also normalize case using the upper() command, for instance.

All three can be run at once, if desired, like upper(trim(regexp_replace("COLUMNNAME", '\s+',' ')))

enter image description here

• Erik: Is this an excel-solution or a QGIS-solution? Some more details would be appreciated.

Answer 2

The work around which I tried was to download OpenOffice calc and save the .xlsx or .csv file directly to a .dbf file, this seemed to remove any invisible characters and would work when joining.

I will update this answer later to add if any of the other answers to my question worked.