Importing Time-Field Type Datas which includes NULL-Values

yifan

Forums

 
Dear ideascripting.com users,
 
as I am working with IDEA, an error occurs if I convert a Char-Field Type to TIME-Field Type after importing from Excel. The problem further explained:
 

  1. The Importing Excel Data includes a column with Time-Field Type, but ‘NULL’-Values are included beside of ‘HH:MM:SS’ values (example: 12:09:03).
  2. IDEA can’t convert ‘NULL’-Values to TIME-Field Type. So I manipulated the Excel Column that all ‘NULL’-Values are replaced with ‘’-Values. This works fine if I want to convert ‘’-Values in IDEA to DATE-Field Type. ‘’-Time Values become ‘0000.00.00’.
  3. But this won’t work for TIME-Field Type. After Converting ‘’-Values to TIME-Field Type, all former ‘NULL’-Values becomes ‘ERROR’.

 
 
 
It there any possibility to handle with ‘NULL’-Values in TIME-Field Type? If there is no solution, I would let the 'NULL'-Values as 'Errors...
 
Best Regards
 

Steven Luciani Wed, 10/17/2018 - 11:12

Hi yifan,

I hope I understood what you wanted. I created a small Excel file that had two fields TIME with null records and time records and TIME1 with blank records and time records.

As I expected, both field were imported into IDEA as Character fields via the Excel import feature.

In the screenshot below the VTIME and VTIME1 field are virtual time fields I created using the same equation each just referenced their appropriate imported time field.

@ctot(time,"HH:MM:SS")

You are correct that via the field manipulation option the null and blank records will show up as errors if you switch from character to time* and apply the HH:MM:SS mask  in the parameter field of the field manipulation window.

I created virtual fields but you can also create regular TIME fields as well with this equaiton just choose TIME as the field time instead of virtual time.

Hope that answers your question.

Regards,

Steven