I am facing the problem that the data I am loading from a dbf file do contain the replacement character �.
The loaded data are e.g. peoples name strings, they do only contain the first letter, then it ends.
I only found out this by using Excel power query for a trial load.
In power query I can see that every second char is this replacement character.
Does anybody have an advice for me how I could eliminate this replacement character during the load into Qlikview from the dbf file ?
Thanks in advance for your help.
I am pretty sure the result we are observing in QV is caused by a bug in ODBC driver itself, because first letters show up in preview itself:
Same is in Excel, when using ODBC:
So you can look for some custom ODBC DBF connector, or you will need to convert your DBF to sth else
I will investigate further.
At the Moment the only Chance is to load or open it in Excel and to eliminate the replacement character with a macro using the command
.Cells.Replace What:=ChrW(65533), replacement:="", Lookat:=xlPart
Even trying to open the dbf in Excel and to save it as a Unicode text file gives the same problems.
The � remains in the strings.
Anyhow, for the Moment being, thank you all for your help.
Maybe it's a matter of specifying the charset within the driver to handle special chars and things like BOM: Re: Chargement de fichier avec des caractères
I don't know if this is the correct way how I set the charset in the connection string.
ODBC CONNECT32 TO [5EMPL.dbf];CharSet=UTF8;
LOAD replace(NAME,chr(65533),'') as NAME,
SQL SELECT *
But again the replace command does not work, only the first letter appears.
I don't think your data is UTF-8, I suspect that it is more likely UTF-16 (or other format) and the 'extra' character is the high order byte of the two byte characters.
There are quite a few variations of UTF-8 and UTF-16 and I am no expert in this area. I would try and find out more about the format from the database administrator. What type of database are you using?
A good question.
The database comes from a software from which I have the trial version up to now.
After my vacation I will buy a full licence for my company.
Only with that I will be able to ask the developer for more details.