The following transform should do what you want,
However if you have the following source data
and you set Col1 up as a Date, then when it reads the empty value it tries to cast it to a Date - which fails.
Having reviewed this functionality we think it should return a 'null' value when no data is present in a cell, which would see the above transform generating the data
We will make the changes in the next point release.
In the meantime you can do this to work around the issue
Where Col1 is defined as containing a "string".