Date values set to '' and not to NULL gives errors during import

[expired user #9515]'s profile image [expired user #9515] posted 8 years ago in Import/Export Permalink

Dear Team,

I am trying to import data into my sql table. HeidiSql 9.3.0.4984 does not set empty Date columns as NULL but marks them with "'" and therefore, I get the following error message.

/ SQL Fehler (1292): Incorrect date value: '' for column 'Date2' at row 1 /

I have shown you part of the sample string that I copied from the log from the bottom, for you to see, where the error is.

I have provided the table information and attached the sample file, which I tried to upload into the DB structure and which fails with above code.

Can you look into this, since I cannot find out, what the problem might be?

Thanks Mike

3 attachment(s):
ansgar's profile image ansgar posted 8 years ago Permalink

You are using HeidiSQL's text file import dialog?

If so, then the file you want to import should have a NULL string where you want to get a NULL value. The file you attached has just empty strings, which means '' in SQL terms.

[expired user #9515]'s profile image [expired user #9515] posted 8 years ago Permalink

yes, I am using the Import Dialog.

ok. fine. If you use this example attached, HeidiSQL converts the empty strings into NULL. So, I thought it would do that all the time and I figured, that you used a logic to check that, bassed on the values of the coloums that should be availible at Import.

Regards Mike

this is the layout for below, which imports perfectly well, even though there are no Null values in there:

CREATE TABLE IF NOT EXISTS `test_empty_date_import` (
  `Date1Type` varchar(45) DEFAULT NULL COMMENT 'Type of calendar date',
  `Date1` date DEFAULT NULL COMMENT 'Calendar date'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1 attachment(s):
ansgar's profile image ansgar posted 8 years ago Permalink

The second file you attached has two specialities:

  • It does not contain a field separator (tab) before the empty date value
  • the very last row does have a tab left to the empty date value, but HeidiSQL assumes in this (and only in the very last column of the very last row) a NULL value if there is no value.

You can try the server parsing (see "Method" in the dialog). But that will also warn you:

Warning (1265): Data truncated for column 'Date2' at row 1

I recommend you either

  • store NULL strings in empty date fields, which HeidiSQL can detect as real NULL values then
  • or set the date columns to VARCHAR(20), and convert them later to DATETIME again
[expired user #9515]'s profile image [expired user #9515] posted 8 years ago Permalink

ok, I will try the later! thanks for your Support on this!

Please login to leave a reply, or register at first.