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

asked 31 Oct '12, 09:46

Satish%20Panithi's gravatar image

Satish Panithi
91447
accept rate: 0%

edited 31 Oct '12, 13:14

Jeff%20Albion's gravatar image

Jeff Albion
10.8k171175

Can you show a sample how the csv files looks.

(31 Oct '12, 11:56) Thomas Dueme...

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

(31 Oct '12, 13:14) Breck Carter

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

(31 Oct '12, 13:15) Breck Carter

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?

(31 Oct '12, 13:19) Jeff Albion

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

(01 Nov '12, 03:43) Satish Panithi
Comment Text Removed

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>
permanent link

answered 01 Nov '12, 07:07

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 02 Nov '12, 13:03

Graeme%20Perrow's gravatar image

Graeme Perrow
9.6k379124

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
1

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

(01 Nov '12, 15:52) Volker Barth

@Graeme: Thanks for correcting the code format!

(02 Nov '12, 13:12) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×76
×18

question asked: 31 Oct '12, 09:46

question was seen: 5,385 times

last updated: 03 Nov '12, 17:02