When we export to excel then i'm facing date formatt issues

[Srinivas]

Hi All,

Please see below code, Here in Qlikview front-end level showing correct format , but when we are export into excel or csv file then it's showing problem.

Date:

LOAD 
Date(OrderDate,'YYYY-MM-DD') as OrderDate,
Date(RETD,'YYYY-MM-DD') as RETD,
Date(CETD,'YYYY-MM-DD') as CETD,
UnitCostOfLine
FROM

(qvd);
STORE Date into D:\Personal\Test.Csv(txt);

Qlikview front-end report see below :

orderdate ,RETD CETD

2014-12-02,2015-02-06,2015-02-06

2014-12-02,2015-02-13,2015-02-13

2014-12-02,2015-02-20,2015-02-20

2014-12-02,2015-02-27,2015-02-27

2014-12-02,2015-02-03,2015-02-03

Csv output : this below one is wrong .... I want to see above qlikview front-end format..

OrderDate	RETD	CETD	UnitCostOfLine
02-12-2014	06-02-2015	06-02-2015
02-12-2014	27-02-2015	27-02-2015
02-12-2014	06-02-2015	06-02-2015
02-12-2014	27-02-2015	27-02-2015
02-12-2014	06-02-2015	06-02-2015
02-12-2014	03-03-2015	03-03-2015
02-12-2014	20-02-2015	20-02-2015
02-12-2014	13-02-2015	13-02-2015
02-12-2014	20-02-2015	20-02-2015
02-12-2014	06-02-2015	06-02-2015
02-12-2014	27-02-2015	27-02-2015
02-12-2014	06-02-2015	06-02-2015
02-12-2014	03-03-2015	03-03-2015
02-12-2014	20-02-2015	20-02-2015
02-12-2014	06-02-2015	06-02-2015

Could you please help on this.

Thanks & Regards,

Munna

[sunny_talwar]

Your front end table doesn't have UnitCostOfLine may be you want to remove that field before you save?

Best,

Sunny

[Srinivas]

Hi Sun,

As of now  UnitCostOfLine is not required but I want to see correct date format in excel level.when am apply in script level code that's worked in qlikview front level.

But after export to excel or csv file through store command then it's not coming correct format.

Advance Thanks,

Munna

[robert_mika]

The only option I see is to save as Text.

After Store,Open the file and fallow the wizard as you get to this stage

Result

[Alejandro_Hernández]

Are you seeing this date format in excel or opening the .csv file in notepad?

This is how the generated .csv looks in notepad:

OrderDate,RETD,CETD,UnitCostOfLine

2014-12-02,2015-02-06,2015-02-06,

2014-12-02,2015-02-27,2015-02-27,

2014-12-02,2015-02-06,2015-02-06,

2014-12-02,2015-02-27,2015-02-27,

2014-12-02,2015-02-06,2015-02-06,

2014-12-02,2015-03-03,2015-03-03,

2014-12-02,2015-02-20,2015-02-20,

2014-12-02,2015-02-13,2015-02-13,

2014-12-02,2015-02-20,2015-02-20,

2014-12-02,2015-02-06,2015-02-06,

2014-12-02,2015-02-27,2015-02-27,

2014-12-02,2015-02-06,2015-02-06,

2014-12-02,2015-03-03,2015-03-03,

2014-12-02,2015-02-20,2015-02-20,

2014-12-02,2015-02-06,2015-02-06,

This is how it looks in Excel:

OrderDate	RETD	CETD
02-12-14	06-02-15	06-02-15
02-12-14	27-02-15	27-02-15
02-12-14	06-02-15	06-02-15
02-12-14	27-02-15	27-02-15
02-12-14	06-02-15	06-02-15
02-12-14	03-03-15	03-03-15
02-12-14	20-02-15	20-02-15
02-12-14	13-02-15	13-02-15
02-12-14	20-02-15	20-02-15
02-12-14	06-02-15	06-02-15
02-12-14	27-02-15	27-02-15
02-12-14	06-02-15	06-02-15
02-12-14	03-03-15	03-03-15

Excel uses the date format you  defined in Control Panel-Regional Settings.

[Srinivas]

HI Robert,

can we change in Qlikview edit script level that would be better, this is only sample file like this I have to export every day lot of files so that can you give any idea.

Advance Thanks,

Munna