Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Hello All
I do attach an example of the dbf file.
Maybe somebody has another idea ?
Best regards
Joerg
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
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
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
I tried with your proposal Marcus,
but unfortunately still get only the first letter from the field.
Thanks
Joerg
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
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