Reformat Date not returning NULL

Hi.

I'm a new user. Downloaded evals of both Visual Importer and Advanced ETL Processor.

I am reading timestamps from QVD files and trying to save to a SQL server table (field format datetime).

In the QVD, fields with valid timestamps are saved corrected to the SQL table. However, some of the date fields are NULL. Visual Importer correctly interprets this and saves a null to the SQL field. However, AETL does not. It instead saves the date 1899-12-30 to SQL. I have tried different combinations of parameters (including those to match Visual Importer) but always the same result. Can you please help? Thanks.

1 Please do not use reformat function load date field use Date format function instead
2 You might be seeing empty strings instead of nulls.

The solution is to truncate the string and convert it to null like in the picture below



Please keep us posted on your progress

Thanks Peter. That worked.

I don't think I fully understand the transformation step though. Why is there a link between the field name and the "Failure" condition in the "If Null" step?

It appears that the "If Null" step is required in AETL where as in Visual Importer it is not. Is that correct? Also, since I have many dates to check and convert, is there a shortcut or do I have to add these steps for each one?

My preference is to modify the source field (in the QVD) so that these steps aren't necessary. I thought I've set it already to NULL, but the Date Format transformation is setting it 1899-12-30 without the "If Null" step. Can you suggest what I can do to the source field so that the "If Null" step is not necessary?

Thank you.


Dave Greene

I don't think I fully understand the transformation step though. Why is there a link between the field name and the "Failure" condition in the "If Null" step?

It is a simple "IF" statement

if value is null or empty sting the result is taken from success otherwise the result is taken from failure

I can see that not all your values are nulls or empty strings

I get it now. Thank you. That makes sense.

Are you able to answer my other questions?

1 It appears that the "If Null" step is required in AETL where as in Visual Importer it is not. Is that correct?

>>This is correct, on top of that Advanced ETL Processor has all the functionally included in Visual Importer.
EG Advanced ETL Processor can create and run Import Scripts
If you do not need any advanced transformations Import Script is a slightly faster option

2 Also, since I have many dates to check and convert, is there a shortcut or do I have to add these steps for each one?

>> You can use the object library for that

www.etl-tools.com/advanced-etl-processor...ble-etl-objects.html

3 My preference is to modify the source field (in the QVD) so that these steps aren't necessary. I thought I've set it already to NULL, but the Date Format transformation is setting it 1899-12-30 without the "If Null" step. Can you suggest what I can do to the source field so that the "If Null" step is not necessary?

>>> It is hard to give any advice without seeing the actual qvd fie