on 2012 Oct 31 9:46 AM
Am using The LOAD TABLE statement to load a csv file to sybase table from .net application
LOAD TABLE dba.CONTACT FROM 'C:\\TextFileCONTACT.txt' DELIMITED BY '\\x09'
Table dba.CONTACT consists of timestamp columns and they allow nulls.
below is the code
string sqlString = "LOAD TABLE " + TableName + columns + " FROM '" + OuputPath + "\\\\" + TableName + ".txt' DELIMITED BY '\\x09'"; //int result = oDBHelper.ExecuteNonQuery(CommandType.Text, sqlString); using (SAConnection conn = new SAConnection(utils.DBMgr.ConnectionStr)) { conn.Open(); using (SACommand cmd = new SACommand(sqlString, conn)) { int result = cmd.ExecuteNonQuery(); } }
Am getting the below error at ExecuteNonQuery line "Cannot convert NULL to a timestamp. The bad value was supplied for column 'DOB' on row 1 of the data file"
but where as when am using an insert statement am able to insert nulls values to timestamp columns insert into tablename values(value1,value2,NULL,NULL .........)
Please suggest how to insert NULL Values to timestamp columns using LOAD Table
You are using LOAD TABLE with FORMAT TEXT (the default), and that expects NULL values in the data file as no value - cf. the docs:
With FORMAT TEXT, a NULL value is indicated by specifying no value. For example, if three values are expected and the file contains 1,,'Fred',, then the values inserted are 1, NULL, and Fred. If the file contains 1,2,, then the values 1, 2, and NULL are inserted. Values that consist only of spaces are also considered NULL values. For example, if the file contains 1, ,'Fred',, then values 1, NULL, and Fred are inserted. All other values are considered not NULL. For example, '' (a single quote followed by single quote) is an empty string. 'NULL' is a string containing four letters.
So in your case the value "NULL" in the data file is treated as a string value that obviously can't be converted to a timestamp.
Solutions would be to
(*) The code:
INSERT INTO <theRealTable> ... SELECT ... , CASE WHEN DOB_VAR = 'NULL' THEN NULL else DOB_VAR END CASE, ... FROM <theTemporaryTable>
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Glad you got it working - and feel free to accept that answer:)
User | Count |
---|---|
75 | |
10 | |
10 | |
10 | |
10 | |
9 | |
8 | |
7 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.