oracle11g - OBIEE exported csv and when importing to Access I get an error

Question

I have a report on OBIEE, the date field I made it Custom format to MM-DD-YYYY I have also try [FMT:dateShort]. Once I exported to .csv I am Linking the file with MS Access 2007 and the date field I get an error #NUM! why is that ?

Answer 1

Either one of these could be the problem/solution:

The #Num! in Access is telling you the cell contains a mix of data; some digitit, some text, for example: 34G (or your '-') would throw a #Num!

You need to go back to the Excel and change the format of any cell containing both numbers and text to "mixed data" then save that sheet before importing to Access.

You can do this with a Macro:

Sub Addspace() 
  Dim cell As Object 
  For Each cell In Selection 
    cell.Value = " " & cell.Value 
    cell.Value = Right(cell.Value, Len(cell.Value) - 1) 
  Next 
End Sub 

Simply highlight cells needing the formatting, run the above macro, then re-save spreadsheet.

Or:

The #Num! error value means that the value in the field is too large (either positively or negatively) to be stored in the field, based on the field's DataType or FieldSize property setting. (https://support.microsoft.com/en-us/kb/209132)

Based on your format of the dates, I would try the first fix before anything else. Date =/= Number