CSV to FGDB table - null date attribute could not be written - containing feature dropped

Hi Takashi,

No there isn't much more in the log than that - essentially the table is created

FileGDB Writer: Created table 'So and So'

The next line it reads

FileGDB Writer: The `Date' attribute could not be written. The containing feature has been dropped.

It then details all the attributes that have been correctly written but the feature has already been dropped.

I have changed my writer to ESRI Geodatabase (File Geodb) - it was set to the open API FGDB which was unnecessary. However my issue now is that it rejects all null values outright and then cannot read the date format for legitimate dates.

The date '30/06/2018' is not valid. Valid forms for a date type are YYYYMMDD, YYYYMMDDHHMMSS, or HHMMSS

Although I have tried to enforce this with a DateTimeConverter immediately before writing

Input Format: %d/%m/%Y$

Output Format: %d/%m/%Y

If I drop these date fields my data will write fine :).

Any other ideas?

Mike

Hi @mikegresham1, as you mentioned, a date/time value should be formatted with the Standard FME Date/Time Format (typically "%Y%m%d" or "%Y%m%d%H%M%S"), when you write the value to a date/time type column with an FME writer. Try setting 'FME' or '%Y%m%d%H%M%S' to the Outpue Format parameter in the DateTimeConvertor.

Hi Takashi,

 

 

Thanks again for the suggestions (and apologies for the slow response).

 

 

I have tried both of those date formats in the "output" format parameter however I still lose the records if any of the date fields have a null record. If all three date fields are populated the record will write.

 

 

As an interim measure I can probably add in a "dummy" date i.e. 01/01/1900 and update these records as null retrospectively but I'm sure FME has a way of handling it and I've just missed it somewhere.

 

 

Thanks again for your help with this.

 

 

Mike

 

 

If the destination is an existing File Geodatabase, the table definition may not allow to store "null" to the fields. Check the field definitions with ArcCatalog or ArcGIS Pro, if the destination dataset is an existing geodatabase.

 

 

The error is different if you try to write to a non-nullable field. I only see "Valid forms for a date type are YYYYMMDD, YYYYMMDDHHMMSS, or HHMMSS" if trying to send a unformatted date to a date field.

 

 

If the error message even after the date formatter with correct FME format is saying "The date '30/06/2018' is not valid" then it sounds like the dateformatter is not working correctly.

 

 

Setting the invalid date attribute to "error" may shed some light

 

Hi @mikegresham1: would you mind sharing a sample of your input data and your workspace for us to look at? If you save your workspace as a Template (.fmwt format), your data will be packaged up with the workspace.

 

Hi Nampreet,

 

 

Apologies for the tardy response - my only excuse is I've been working on other things :). Unfortunately the data is of a confidential nature so I've attached an abbreviated *.fmwt with some dummy data (I've removed almost all the fields apart from the date fields and a couple of others - there are 60 plus fields in the original). I still get the same issue when I run this workspace i.e. the inability to write the dates

 

 

gen-csv-2-fgdb-testing.fmwt

 

 

I'd be interested to hear your feedback as I've had to revert to ESRI modelbuilder to help me for this one.

 

 

Cheers,

 

Mike

 

Hi Nampreet,

 

 

Apologies for the tardy response - my only excuse is I've been working on other things :). Unfortunately the data is of a confidential nature so I've attached an abbreviated *.fmwt with some dummy data (I've removed almost all the fields apart from the date fields and a couple of others - there are 60 plus fields in the original). I still get the same issue when I run this workspace i.e. the inability to write the dates

 

 

gen-csv-2-fgdb-testing.fmwt

 

 

I'd be interested to hear your feedback as I've had to revert to ESRI modelbuilder to help me for this one.

 

 

Cheers,

 

Mike

 

 

The output format in your date time format is not the FME format as previously advised.

 

 

I can write the data to a file gdb no problem if this is changed. You need to connect up the rejected port as well

 

 

 

 

Hi @takashi, @egomm, @Nampreet,

Yes, yes, yes - we have an answer. As Takashi mentioned before the valid date formats require the FME standard date/time format "%Y%m%d" or "%Y%m%d%H%M%S" and although I tried this I didn't cotton on to the fact that I needed to connect the rejected port as well to write the records with null dates - thanks @egomm for pointing this out. And of course you need to have the "Rejected Feature Handling" set to "Continue Translation".

Thanks for your help with this everybody ... onwards and upwards.

Cheers,

Mike