QlikView not recognizing dates in Excel sheet

[montubhardwaj]

Hi Community,

I have some Dates values in the Excel which QlikView is not able to read. While importing itself, it is not showing any values fetched from Excel sheet.Please see below snapshot:

I don't want to make any changes in the Excel Format. Is there anything we can do from QlikView side so it is able to read values from excel.

many thanks in advance!

Regards,

Sharma

[#2]

Hi Sharma,

I tried in QlikView 9.0 SR 5. it is working here.

[sunil2288]

Hi Sharma,

Although its showing number format while loading the date field from excel sheet but it will treat it as Date only after loading

Else you can use The Date( ) function to the field that is present in Formatting Function type while loading..

I am sending you the attatched document of sucess fully load of the date value..

Thanks

Sunil

[sunil2288 · Accepted solution]

Hi Sharma,

Although its showing number format while loading the date field from excel sheet but it will treat it as Date only after loading

Else you can use The Date( ) function to the field that is present in Formatting Function type while loading..

I am sending you the attatched document of sucess fully load of the date value..

Thanks

Sunil

[montubhardwaj]

@Raj: I am usinmg 8.5 only. Anyway thanks for your concern.

@Sunil: I have already tried that but it is not helping me. Even I tried the code you have given me but still not able to achieve it. I am using QV 8.5.

is anything to do with the settings?

Thanks!

[sunil2288]

HI Sharma,

I think you have problem in the excel sheet.. the field containing the Date value is not recognizing by the Qlikview.

I have also faced with the same problem and then copied the Excelsheet to another sheet and formatted the date field with correct date field then it was loaded.

And also you can set the Date Format in the Edit script by default it comes as 'm/d/yyyy' you can also set it like that..

But my thought is you have the problem with your 'Microsoft excel sheet'

[#7]

You can try with upgrading this to QV 9.0. As there might be some bugs in 8.5 version. There is nothing wrong in your excel sheet. It depends on the interface (ETL Part) which is not allowing it to read data from that format.

Regards,

Raj Kishor

[montubhardwaj]

Hi Sunil,

I don't want to do any changes in my excel sheet as it is provided by client and I do't have access to change anything in this. I am just trying to pull the data from this but its not working out. Is there something we can do without doing the changes in the Excel sheet ?

Any help is much appreciated.

[sunil2288]

HI Sharma,

I am not telling you to change any thing in the data value in the Excel sheet.

I just want to tell you change in the settings of your 'Microsoft Excel Sheet'.

There you need to set the format as timestamp as 'mm/dd/yyyy hh:mm:ss'

You can try it out.

Why i am telling you Bcoz the .xlsx file what you have posted is working fine with me..

but at your end it's not..

[montubhardwaj]

Hi Sunil/Raj,

I figured out the problem. QV was not able to read it from Excel 2007 but it is able to read from Excel 2003. So now I have changed my Excel to 2003 and is working all fine. Thanks for your suggestions.