New post
Question
· May 26, 2021

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

#Data Import and Export #SQL #Caché

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
Discussion (4)3
Log in or sign up to continue
Peter Steiwer · May 27, 2021

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

Class:

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

CSV:

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:
C:\Users\psteiwer\Documents\DC\testimport.csv
Charset:
<Device Default>
Schema:
DC
Table:
TestImport
Columns are delimited by:
Special Character: ,
First row contains column headers?
No
String quote:
double
Date format:
YYYY-MM-DD
Time format:
hh:mm:ss
TimeStamp format:
ODBC Format
Disable validation?
No
Defer Index Building with %SortBegin/%SortEnd:
No
0 0