Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
montubhardwaj
Specialist
Specialist

QlikView not recognizing dates in Excel sheet

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:

error loading image

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

1 Solution

Accepted Solutions
sunil2288
Creator III
Creator III

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

View solution in original post

9 Replies
Not applicable

Hi Sharma,

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

sunil2288
Creator III
Creator III

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
Creator III
Creator III

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
Specialist
Specialist
Author

@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
Creator III
Creator III

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'

Not applicable

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
Specialist
Specialist
Author

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
Creator III
Creator III

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
Specialist
Specialist
Author

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. Yes