3

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?)

Improve this question
3
  • 2
    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.
    – PolyGeo ♦
    Aug 5, 2019 at 0:32
  • 2
    I've had similar issues that were solved by searching for and deleting double (and triple, and so forth) blanks, both leading and trailing.
    – Stu Smith
    Aug 5, 2019 at 0:44
  • 2
    Try to use Trim Function in Excel: support.office.com/en-us/article/… to remove any white spaces in the Name field.
    – ahmadhanb
    Aug 5, 2019 at 5:41

2 Answers 2

Reset to default
2

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

Improve this answer
2
  • Is this an excel-solution or a QGIS-solution? Some more details would be appreciated.
    – Erik
    Aug 5, 2019 at 6:36
  • 1
    This is a fully QGIS solution using the Field Calculator.
    – Saijin_Naib
    Aug 5, 2019 at 13:26
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.

Improve this answer

Your Answer

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

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