Stella Ticker · May 26, 2021

SQL Data Import Wizard with into table with %Library.TimeStamp and %Boolean datatype columns

I am trying to populate a table using the sql Data Import Wizard. The input file is a tab delimited text file. But the import keeps failing with a 104 error showing validation for the columns which use %Library.TimeStamp and %Boolean datatypes is failing. Yet when I insert values into the table through a SQL insert command, the values get saved correctly in the table.

For the TimeStamp format in the wizard form, I am choosing YYYY-MM-DD-HH:MI:SS because  there was no option for this format: YYYY-MM-DD HH:MM:SS. 

But even after I edited the time stamp column to use a dash between the DD and HH like is shown in the former, it did not work! 

Here are examples of the errors:

For MyTimeStampField value=2021-05-26 11:45:40  and MyBooleanField=0 the error is:-

104    Field validation failed in INSERT, or value failed to convert in DisplayToLogical or OdbcToLogical : 
Field 'MySchema_MyTableName.MyTimeStampField' (value '2021-05-26 11:45:40 ') failed validation Field MySchema_MyTableName.MyBooleanField' (value '0') failed validation


Has anyone ever successfully used the data import wizard to import data into a into table with %Library.TimeStamp and %Boolean datatype columns? What did your input look like and what values did you choose for the TimeStamp Format?


Thank you for reading

Product version: Caché 2017.1
0 182
Discussion (4)3
Log in or sign up to continue

The correct timestamp format is YYYY-MM-DD HH:MM:SS but according to the error message, your data does not meets this format.

104    Field validation failed in INSERT, or value failed ...MyTimeStampField' (value '2021-05-26 11:45:40 ') 

You see the space or tab character after the seconds? 

@Julius Kavay 
That space is being added by the error handler. There is no space between the last character and the tab delimeter in the input data. 

I just tested and it worked OK for me. I used the following:


Class DC.TestImport Extends %Persistent
Property TS As %TimeStamp; 
Property Bool As %Boolean; 


2021-05-27 17:43:15,0
2021-05-27 17:51:13,0
2021-05-27 17:53:11,1

Import Settings:

File Name:
<Device Default>
Columns are delimited by:
Special Character: ,
First row contains column headers?
String quote:
Date format:
Time format:
TimeStamp format:
ODBC Format
Disable validation?
Defer Index Building with %SortBegin/%SortEnd: