|
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:
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> Volker Barth you are absolutely right omit the NULL in the data file and simply supplied nothing between the column delimiter (TAB in your case) This worked for me like a charm Thanks a lot for the solution.
(01 Nov '12, 08:36)
Satish Panithi
Replies hidden
@Graeme: Thanks for correcting the code format!
(02 Nov '12, 13:12)
Volker Barth
|
Can you show a sample how the csv files looks.
Does the CSV file contain the characters 'NULL' in the position for DOB? as in xxx,NULL,yyy?
If so, that isn't going to work; try omitting the value as in xxx,,yyy
BTW, DELIMITED BY 'x09' is for a tab-delimited file, not comma-separated CSV
What is the actual delimiter used in the data file? If it's a CSV (Comma Separated Values) file, you would generally use:
(which is also the default delimiter).
Or do you actually see tab characters (0x09) in the text file used as field delimiters?
Am using tab delimited text file and below is the format of how it looks 1918CONS65543 NULL NULL NULL NULL NULL NULL 1918 CONSTRUCTION 555-888
And am passing NULL value for the timestamp column as it allows null