cancel
Showing results for 
Search instead for 
Did you mean: 

unable to load null values that represent a timestamp value

Former Member
5,782

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
Participant
0 Kudos

Can you show a sample how the csv files looks.

Breck_Carter
Participant
0 Kudos

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
Participant
0 Kudos

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

jeff_albion
Advisor
Advisor
0 Kudos

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
0 Kudos

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

Accepted Solutions (0)

Answers (1)

Answers (1)

VolkerBarth
Contributor

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
0 Kudos

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
Contributor

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

VolkerBarth
Contributor
0 Kudos

@Graeme: Thanks for correcting the code format!