Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

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

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

17 Replies
Highlighted
Not applicable

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:

Screen Shot 08-10-16 at 07.54 PM.PNG

Same is in Excel, when using ODBC:

Screen Shot 08-10-16 at 07.56 PM.PNG

So you can look for some custom ODBC DBF connector, or you will need to convert your DBF to sth else

BR,

Kuba

Highlighted
Creator II
Creator II

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

Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Creator II
Creator II

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

Highlighted

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. 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Creator II
Creator II

Dear Jonathan

Also with UTF-16 it does not work.

Best regards

Joerg

Highlighted

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?

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Creator II
Creator II

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