Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
omyahamburg
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
Not applicable

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

omyahamburg
Creator II
Creator II
Author

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

omyahamburg
Creator II
Creator II
Author

Hello All

I do attach an example of the dbf file.

Maybe somebody has another idea ?

Best regards

Joerg

Not applicable

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

omyahamburg
Creator II
Creator II
Author

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

marcus_sommer

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

omyahamburg
Creator II
Creator II
Author

I tried with your proposal Marcus,

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

Thanks

Joerg

marcus_sommer

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

omyahamburg
Creator II
Creator II
Author

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