unable to load null values that represent a timestamp value

[Former Member]

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

[thomas_duemesnil]

Can you show a sample how the csv files looks.

[Breck_Carter]

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

[Breck_Carter]

BTW, DELIMITED BY 'x09' is for a tab-delimited file, not comma-separated CSV

[jeff_albion]

What is the actual delimiter used in the data file? If it's a CSV (Comma Separated Values) file, you would generally use:

LOAD TABLE ... DELIMTED BY ','

(which is also the default delimiter).

Or do you actually see tab characters (0x09) in the text file used as field delimiters?

[Former Member]

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

[VolkerBarth]

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

• omit the NULL in the data file and simply supply nothing between the column delimiter (TAB in your case) or
• if you can't modify the data file - load into a temporary table that has char columns instead of timestamps (and therefore can accept 'NULL' as value) and then fill the real table by selecting from the temporary table and use something like the code below (*) to replace the string 'NULL' with the special NULL value.

(*) The code:

INSERT INTO <theRealTable> ...
SELECT ... , CASE WHEN DOB_VAR = 'NULL' THEN NULL else DOB_VAR END CASE, ...
FROM <theTemporaryTable>

[Former Member]

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.

[VolkerBarth]

Glad you got it working - and feel free to accept that answer:)

[VolkerBarth]

@Graeme: Thanks for correcting the code format!