topic Re: How to replace chrw(65533) during load from dbf-file in QlikView

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

omyahamburg — Wed, 25 Nov 2020 16:16:04 GMT

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

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

Tue, 09 Aug 2016 18:14:37 GMT

Hello Jörg,

Simple Replace will not help?

Characters:
LOAD
ID,
Replace(Text,'�','') as Text
;
LOAD * INLINE [
    ID, Text
    1, test
    2, test2�
    3, hello�
    4, aaaaa�
];

BR,

Kuba

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

omyahamburg — Tue, 09 Aug 2016 18:34:33 GMT

Dear Kuba

Unfortunately not.

My script

LOAD ID, replace(NAME,'�','') as NAME;
SQL SELECT ID, NAME
FROM `xxxx`\`5EMPL`;

still gives me only the first letter of the Name field.

The dbf source, when I open it in Excel and run a query on it with power query I can see the character:

Best regards

Joerg

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

omyahamburg — Tue, 09 Aug 2016 18:51:42 GMT

Hello All

I do attach an example of the dbf file.

Maybe somebody has another idea ?

Best regards

Joerg

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

Tue, 09 Aug 2016 19:27:05 GMT

Hey again,

So I installed ODBC driver and loaded file into qlikview. even on preview only first letters are showed, as you describe.

The only way I forced it to work, was to:

1. open your file using Libre Office

2. Saving it as ods file

3. Opening ods file and saving again as dbf (Unicode UTF8).

DBF file created this way was properly recognised by QV. But if you could do it like that you would probably just saved it as excel in the first place and did not bother with DBF

BR,

Kuba

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

omyahamburg — Tue, 09 Aug 2016 19:51:34 GMT

Dear Kuba

I feared already that there might be no solution to load it directly into Qlikview.

Have implemented days ago a macro to import the dbf into Excel, remove this replacement character and replace also others, but this is even for a single file somehow a bit complicated.

Nevertheless, thanks a lot for your efforts.

Best regards

Joerg

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

marcus_sommer — Tue, 09 Aug 2016 20:11:11 GMT

You could use keepchar() to filter yor data - maybe in this way:

Chars:

load concat(Char, '') as Char;

load chr(recno() + 31) as Char autogenerate 255 - 31;

let vChar = peek('Char', 0, 'Chars');

drop table Chars;

Data:

load keepchar(AnyChars, '$(vChar)') as AnyChars inline [

AnyChars

a�a�a

b�b

c�c

];

- Marcus

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

omyahamburg — Wed, 10 Aug 2016 07:11:17 GMT

I tried with your proposal Marcus,

but unfortunately still get only the first letter from the field.

Thanks

Joerg

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

marcus_sommer — Wed, 10 Aug 2016 07:48:23 GMT

My suggestion worked on a field-level and could be generally extended to a file-level by loading the file with fixed lenghts and cleaning/adjusting while these load. Afterwards this could be stored or read from the document but in your case simply appiled it would destroy the specific file-structures from the dbf-file. It might be possible with a detailed knowledge of these file-structure to build a working load-statement but the efforts would be enormously.

Therefore I think you will need an approach like suggested by jakub.szurogajlo (natively opened by an appropriate tool and storing within a different format - maybe automated with some macro-stuff).

- Marcus

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

omyahamburg — Wed, 10 Aug 2016 08:18:50 GMT

I agree with you and will try to solve it with Excel macros to store it in a different format

and then load it from there.

Best regards

Joerg

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

Wed, 10 Aug 2016 17:57:43 GMT

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

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

omyahamburg — Wed, 10 Aug 2016 20:22:45 GMT

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

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

marcus_sommer — Thu, 11 Aug 2016 04:56:23 GMT

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

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

omyahamburg — Thu, 11 Aug 2016 08:19:41 GMT

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

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

jonathandienst — Thu, 11 Aug 2016 08:35:22 GMT

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.

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

omyahamburg — Thu, 11 Aug 2016 09:31:05 GMT

Dear Jonathan

Also with UTF-16 it does not work.

Best regards

Joerg

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

jonathandienst — Thu, 11 Aug 2016 11:24:49 GMT

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?

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

omyahamburg — Thu, 11 Aug 2016 11:56:04 GMT

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