Difference in date format between .xlsx and .csv file in obiee 12c

3364275

Hi Team,

In obiee 12c we found a different issue while exporting the report into .xlsx format and .csv format. In .xlsx format date value is showing correct (17-may). But same report in .csv format it is showing (17-06-2018). which is wrong. It should show (17-06-2017). Please help me on this.

Note: These column is direct pull from the database.

Thanks in Advance.

Regards,

Somi R Chinta.

Robert Angel

In your first example you don't give a year - "showing correct (17-may)." - what is the value in the OBIEE analysis, is it a date field or is a text field and exactly what is the value there?

3364275

Hi Robert,

Its a direct pull from the database. we didn't use any column format. In database it is showing (17-may). So in obiee analytics it is showing correct value (17-May). But if we export it to .csv format it showing 17-05-2018.

[Deleted User]

3364275 wrote:Hi Robert,Its a direct pull from the database. we didn't use any column format. In database it is showing (17-may). So in obiee analytics it is showing correct value (17-May). But if we export it to .csv format it showing 17-05-2018.

CSV is basically a "pure" data representation. so I kind of doubt that statement.

If your DB stores '17-may' as a string with a lower-case "m" then it is pretty impossible that OBI shows the string '17-May'.

Next: If the CSV - which is as I said a very pure data representation - then the actual CSV will contain the string '17-May' (or *m*ay). So if you open the CSV in Notepad and not Excel - what does it REALLY contain?

Because the chances are extremely high that you're just hitting Excel being stupid Excel as usual and trying to be too clever for its own good.

Robert Angel

But is the underlying field text or date?

And, if you don't know / cannot find this out, can you try putting a format on the date that includes the year element to see exactly what is stored in the source database?

Robert Angel

You are speaking aloud my internal thought processes exactly, but I was trying to get to the 'proof' for the user by getting them to see what is really in their system... and then lay out the detail of the 'why' of it - excel's smartness doing something dumb...

[Deleted User]

Whenever you rely on some software or tool do do something on its own and it being correct....you're going down a slipperly slope.

By default I never believe any output (or input for that matter) until I've checked it myself in detail.

"Of COURSE we are delivering the correct data!" <-- Famous last word I heard waaaayyyy too often.

Robert Angel

Yes, I was motivated by the same bias, trying to get to some objective evidence...

3364275

Hi,

Just an FYI.

1)For that date column in obiee RPD is Varchar(50).

2)In database date value eg:- Nov-17,Oct-16...

3364275

Please let me know if the below screenshot helps.

Robert Angel

So, these are 'Accounting Periods' - Nov-17 etc, not dates at all.

When excel makes Nov-17 11/17/2018 it is entirely erroneous.

So you need to use a date field in your export which is fully formed and stored as a date, then your export to excel and csv will be meaningful.