TOAD Data Point 3.6.1 import of empty Excel cell into Oracle DB

Toad Data Point
1

Hi, I’m using TOAD Data Point 3.6.1.3294. When I’m importing empty XLSX fields with date format into Oracle table the imported value is ‘01.01.0001 00:00:00’ instead of expected {null} value. When the same task is performed using old TOAD Data Point 3.3.0.1526 everything is Ok and null values are imported. I’m totally puzzled.

Please help.

BR,

Mario

2

To Mr. Mario Velchev.

I regret I was unable to reproduce this issue. If you could provide us a sample XLSX file that reproduces on your end, we will be able to move forward from there.

How to attach a file to a post

  1. Post a Reply using Rich Formatting
    5step.png

  2. Compose a message

  3. Select the Options tab next to Compose, then click Click to Add to add the xlsx file
    7step.png

  4. Post when you’re ready!

3

Hi Joshua,

Please find attached following information: the data for upload into the table (xlsx and csv formats) and the script for table generation.

BR,

Mario
tmp.rar (8.52 MB)

4

Mario,

What database are you importing the data into? Sybase ASE does not allow null dates, it will default the date. If not Sybase you may have a database setting that is defaulting null values to basically day 1 of the calendar.

5

Also, SQL Server will default a null value to a date if you use certain functions like Coalesce.

6

Several people tried importing this file on Oracle and SQL Server and we always get null values on end_dtm. We cannot reproduce your issue.

7

We are using Oracle 11.2.0.4 DB. I’m using Oracle 11.2.0 x64 client on my MS Win 7 x64 desktop.

8

On the laptop with MS Win 7 x86 I’m using Oracle 10.2 client x86 and TOAD Data Point 3.3.0.1526. Using the laptop I don’t have the described above problem.

BR,

Mario
oracle.xlsx (10.8 KB)

9

I thought maybe the 64 bit client was the issue but I tried that and the null date column imported as null. What date format are you using in the grid? Look in Options | Grid

10

I’m using General date/time with seconds pattern (G). On the same machine I have old version of TOAD 3.4 and I don’t have such problems when I’m importing null values from xlsx files.

11

I tried several combinations including uncheck this checkbox but cannot reproduce
the issue you reported. Could you provide your import template for us to investigate?

12

Hi Kiki,

Please find it atached.
import.rar (1.07 KB)