How to replace chrw(65533) during load from dbf-file

[omyahamburg]

Hello all

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.

Best regards

Joerg

[#2]

Hey,

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

BR,

Kuba

[omyahamburg]

Dear Kuba

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.

Best regards

Joerg

[marcus_sommer]

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

- Marcus

[omyahamburg]

Dear Marcus

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,
FIRSTNAME;
SQL SELECT *
FROM `xxx\DATEN`\`5EMPL`;

But again the replace command does not work, only the first letter appears.

Best regards

Joerg

[jonathandienst]

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. 

[omyahamburg]

Dear Jonathan

Also with UTF-16 it does not work.

Best regards

Joerg

[jonathandienst]

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?

[omyahamburg]

Hi Jonathan

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.

Joerg