Special Characters in the excel makes makes the data truncated when we load into the Qlikview

[naveen341]

Hi,

  I have an excel file of shipment Details .

  In the Address or the name fields It contains a different kinds of special characters like &,Alpha,Betta and many more .

  example: "Dißerner Bruch 81"

The problem when I import the file into QlikView as my data source, the left part along with the special character is truncated and displayed as

  "erner Bruch 81".

I cannot edit the excel Files as it contains millions of record on daily basis. But one thing I noticed is when I copy or do "Save as " the same file then it reads the complete data as "Dißerner Bruch 81"

  All characters before the "ß" are truncated and couldn't read by QlikView.

  Any help on getting through this?

Thanks in Advance

Regards,

Naveen.

[avinashelite]

could you please share the sample data too , so that we could try loading ??

[naveen341]

I am not able to send the excel files data looks like this

[#4]

Hi Naveen, have you got the solution? I'm facing the same as you and unsure how to fix it as the there's a lot of data in the excel file. It is not feasible to find one by one on the special characters and replace it as this file is overwrite every hour.

[naveen341]

hi,

You Can use this script to encode the excel files

let

vPath='D:\DistributionGroups\*.xls';

LET vFileName  = subfield(vPath,'\',SubStringCount(vPath,'\')+1);

FOR

EACH sFileName IN FILELIST ('$(vPath)')

ODBC

CONNECT32 TO [Excel Files;DBQ=$(vFileName)];

SQLTables;

DISCONNECT;

['$(sFileName)']:

NoConcatenate

LOAD [Age Group] as ReadMemberAge ,

     Class as ReadMemberClas,

     [Insured Relation] as ReadMemberRelation,

     ReadNumber as Numb,

    [Insured Relation] & '|' & [Age Group] & '|' & Class as tarifKey

    

from [$(sFileName)](ooxml, embedded labels, table is Sheet1);

DISCONNECT;

NEXT

sFileName