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.
LOAD ID, replace(NAME,'�','') as NAME;
SQL SELECT ID, NAME
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:
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
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.
You could use keepchar() to filter yor data - maybe in this way:
load concat(Char, '') as Char;
load chr(recno() + 31) as Char autogenerate 255 - 31;
let vChar = peek('Char', 0, 'Chars');
drop table Chars;
load keepchar(AnyChars, '$(vChar)') as AnyChars inline [
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).