topic Re: Loading problem Excel file in QlikView

Loading problem Excel file

fatihsahin — Sun, 11 Oct 2015 11:13:37 GMT

Hi community,

I have a strage Problem during loading from an Excel file:

I have one field "category":

In following structure:

Re: Loading problem Excel file

lironbaram — Sun, 11 Oct 2015 11:23:47 GMT

how exactly is your script written

because on a simple load i can't reproduce your error

Re: Loading problem Excel file

fatihsahin — Sun, 11 Oct 2015 11:25:20 GMT

No Transformation - nothing. Just straight loading.

Re: Loading problem Excel file

petter — Sun, 11 Oct 2015 11:25:34 GMT

It might be that QlikView interprets this a bit too much ... the values from Excel. I am not able to reproduce it.

But you could try to do this in your load - as the Text() function will effectively prevent Excel from doing the possibly error prone interpretation:

LOAD

Text( Category ) AS Category

FROM

yourExcelFile.xls (ooxml .... ) ;

Re: Loading problem Excel file

fatihsahin — Sun, 11 Oct 2015 11:32:41 GMT

Hi Petter,

thanks for the tip but I have already tried "text" but no success.

The only difference to the rest of the values is , that this value has a "&" sign but really not sure what the Problem here is.

Re: Loading problem Excel file

sunny_talwar — Sun, 11 Oct 2015 12:29:04 GMT

Like the other mentioned, I did not have any issue loading the complete value ("3.ABD & BCD") with a simple load statement. This is what I used:

Table:

LOAD Category

FROM

[Community_186332.xlsx]

(ooxml, embedded labels, table is Sheet1);

I don't know if that is going to help, but try creating another qvw file and re- add this particular Excel file using Table Files... and see if this problem persist for you.

Re: Loading problem Excel file

petter — Sun, 11 Oct 2015 12:54:24 GMT

Then it seems like your load have the field number 1 mispelled. QlikView is case sensitive when it comes to field and column names. And it seems that you get an empty string ...

Re: Loading problem Excel file

petter — Sun, 11 Oct 2015 12:55:21 GMT

Could you please share the part of the load script where you have your load statement and possible attach the Excel-file or the first few rows...

Re: Loading problem Excel file

fatihsahin — Sun, 11 Oct 2015 12:57:56 GMT

I have now observed that this Problem always occurs if the value contains "&".

Whenever a "&" occurs it is only taking the part after the "&".

Could someone help based on that help?

Would it help to replace this sign with chr() ?

Regards

Re: Loading problem Excel file

petter — Sun, 11 Oct 2015 13:01:16 GMT

You need to share the script and a sample Excel file with us...

Re: Loading problem Excel file

sunny_talwar — Sun, 11 Oct 2015 13:07:28 GMT

Can you make a dummy Excel with the one row containing $ sign and share the script you would use to load the file into QlikView? This way we might be able to see what is going wrong.

Re: Loading problem Excel file

sunny_talwar — Sun, 11 Oct 2015 13:11:46 GMT

or can you try to run this qvw file on your computer and sharing a image of the table box object just like how I did in my previous post?

Re: Loading problem Excel file

nizamsha — Sun, 11 Oct 2015 13:56:28 GMT

I checked this using inline its working fine

Re: Loading problem Excel file

fatihsahin — Sun, 11 Oct 2015 14:22:08 GMT

Cant upload the original file and if I copy & paste into another Excel the Problem do not anymore occur.

Anyhow I have to fix this Problem with that file.

I guess it something about the Excel - its a xlsx Format.

Re: Loading problem Excel file

Anonymous — Sun, 11 Oct 2015 14:30:03 GMT

hi fatih,

May be It is the relation of the data fields you have in the file.

please check if the other values in the row for value "3.ABD & BCD" are loaded properly

Re: Loading problem Excel file

nizamsha — Sun, 11 Oct 2015 14:30:13 GMT

Just upload only the 3 record how its sit in ur excel

Re: Loading problem Excel file

Anonymous — Mon, 12 Oct 2015 08:20:29 GMT

Hi,

Please Share the sample Excel file .

Thanks,

Bunny