topic Re: Special Characters in the excel makes makes the data truncated when we load into the Qlikview in QlikView

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

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

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.

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

Chanty4u — Fri, 25 Mar 2016 16:18:51 GMT

hi replace that field by empty string

Replace( Field, '_x000D_' ,'')

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

Gysbert_Wassenaar — Fri, 25 Mar 2016 16:24:45 GMT

Are you sure your source document is an excel file and not a csv file? It sounds like it is a csv file with unicode characters and you try to load it as an ascii file.

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

naveen341 — Fri, 25 Mar 2016 16:31:09 GMT

HI Chanty .

It is still the same .

Any other suggestions?

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

naveen341 — Fri, 25 Mar 2016 16:31:40 GMT

HI Wassenaar.

Yes i am sure its a excel file only

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

Chanty4u — Fri, 25 Mar 2016 16:44:48 GMT

try to create

Replace(Your Field,'ß',' ') as Youfield,

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

Gysbert_Wassenaar — Fri, 25 Mar 2016 16:45:50 GMT

Can you post the load statement that loads that file?

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

naveen341 — Fri, 25 Mar 2016 18:36:45 GMT

BUt ß is not the only special character in that excel i have many such kind of special characters

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

naveen341 — Fri, 25 Mar 2016 18:37:24 GMT

LOAD [Shipment Details],

[Shipped (ASN)],

[Shipment ID],

[Delivered to Final Dest.],

[Purchase Order],

[Final Destination (Shipment)],

[Final Destination Addr1],

[Final Destination City],

[Final Destination Country],

[Final Destination Postal Code],

[Final Destination State],

Status,

[BL/AWB/PRO],

Container,

[Pro #]

FROM

(ooxml, embedded labels, table is [View Shipments]);

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

naveen341 — Fri, 25 Mar 2016 18:40:46 GMT

i dont want to delete or replace the special character how can i get the output as "Dißerner Bruch 81 " ?

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

avinashelite — Mon, 28 Mar 2016 07:59:53 GMT

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

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

naveen341 — Tue, 29 Mar 2016 14:50:32 GMT

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

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

Fri, 29 Apr 2016 03:16:04 GMT

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.

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

naveen341 — Fri, 27 May 2016 15:37:46 GMT

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;

sFileName