Hi @mlauber71,
I tried all of these resources and ended up working with the various date and time formats workflow. I used the Double to Int, Unix Timestamp to Date&Time, as well as the Legacy Date&Time. With all of these, I had to add a preset date/time(legacy) to fill in 1/1/1900 on any records with null date values. After all of this, I found that I was still having trouble with one of the 4 date columns.
I decided to take a look at the formating in the excel file. On the surface, nothing was amiss, but when I looked at the column, I noticed the alignment on the troublesome date field was to the left, meaning it was in text format despite the format of the cell being selected as Date. I created a second column and used the following formula to force format the column as a date: =DATEVALUE(TEXT(S2,“mm/dd/yyyy”))
Typically, after using a formula I select the column, copy and paste the values, but in this instance - I left the formula in the column and named it with a prefix to distinguish it from the original column. After saving my changes I reset and executed the file import node and executed all of the aforementioned nodes. In the end, I found that I had a date column! Situation neutralized.
Now here is where I find the next bit of trouble.
I have all of my columns formatted properly, I need to remove the time portion of the datetime on all 4 columns. I added the Modify Time Node - after the preset date/time nodes and before the DB Writer node. When I try to configure the node, the 4 date fields don’t populate the include section. In fact, there are no data elements in the options tab. I execute it successfully (or so it seems) and move on to the DB Writer Node (with a successful database connection node).
I set up all of the Output Type Mappings and no matter which data types I use for the dates it fails. I tried every source type to output type and no matter what I do it fails. There was an old post that mentions the exact error message ( Error DB Insert - Output Type Mappings and it seems to have been abandoned. So this remains unresolved at the moment, however, when it comes to the date conversion issues; part of it was resolved using the workflow and for one of the dates, I used the formula mentioned above in the excel file to force the right format.
Should I start a new topic with the error message that is stopping me in my tracks?
Again, many thanks for your help with this project.
Best wishes,
Noemi