Data type-related error upon loading a CSV file into a partitioned table using loadTextEx

Question

Supposing there is a CSV file containing the following columns: “Code“, “Timestamp“, “Opening Price“, “Highest Price“, “Lowest Price“, “Closing Price“, “Trading Volume“, “Trading Amount“, and “Adjustment Coefficient“. I want to use the loadTextEx function to load this CSV file into a partitioned table quotes in a DFS database and split the original “Timestamp“ column into columns “MyDate“ of DATE type and “Time“ (e.g., split “2024.01.29 09:31:00“ into “2024.01.29“ and “09:31:00“). “Code“ and “MyDate“ are the key columns of the partitioned table.

The following is my script:

dateDomain = database("", VALUE, date(2018.05.01..2024.07.01))
symDomain = database("", RANGE, string('A'..'Z') join `ZZZZZ)
stockDB = database("dfs://stockDB", COMPO, [dateDomain, symDomain])
quoteSchema = table(10:0, `Code`Timestamp`Opening Price`Highest Price`Lowest Price`Closing Price`Trading Volume`Trading Amount`Adjustment Coefficient`MyDate`Time, [STRING,DATETIME,DOUBLE,DOUBLE,DOUBLE,DOUBLE,DOUBLE,DOUBLE,DOUBLE,DATE,STRING])

stockDB.createPartitionedTable(quoteSchema, "quotes", `MyDate`Code)

def mytransform(mutable t){
   t.rename!("col0", "Code");
   t.rename!("col1", "Timestamp");   
   t.rename!("col2", "Opening Price"); 
   t.rename!("col3", "Highest Price"); 
   t.rename!("col4", "Lowest Price"); 
   t.rename!("col5", "Closing Price"); 
   t.rename!("col6", "Trading Volume"); 
   t.rename!("col7", "Trading Amount"); 
   t.rename!("col8", "Adjustment Coefficient"); 
   t.update!(`Time, <[substr(string(t.Timestamp),11,19)]>);
ccc=date(t.Timestamp)

t[`MyDate] = <[ccc]>;



      return t  
}

tmpTB=loadTextEx(dbHandle=stockDB,tableName=`quotes,partitionColumns=`MyDate`Code,filename='D:/SH600000.CSV',transform=mytransform,skipRows=1);

However, an error occurs:

The column [MyDate] expects type of DATE, but the actual type is STRING.

How to solve this error?

Answer 1

It is recommended to convert the data type of “Time“ from STRING to TIME, and that of “Code“ from STRING to SYMBOL. Then, redefine the mytransform function as follows:

def mytransform(mutable t){
   t[`MyDate]=date(t[`col1])
   t[`Time]=time(t[`col1])
   return t  
}