Working with Dates from an R Source Table

Hi everyone,

After successfully getting my R integration going :partying_face:, I am having a hard time with the concept of converting a date that is coming in from an XLSX file as double (data type) to a yyy-mm-dd format.

I started with R Source Table Read XLSX node followed by an R Snippet node. I can’t figure out how to code it. Granted, this is my first attempt at working with R code and I am in the early noob phase :nerd_face:

Here is what I have written up:
knime.out <- knime.in
knime.in$“filing_date” <- as.Date(knime.in$filing_date , format = “%yyyy/%mm/%dd”)
knime.in$“effdate” <- as.Date(knime.in$effdate , format = “%yyyy/%mm/%dd”)
knime.in$“dob” <- as.Date(knime.in$dob , format = “%yyyy/%mm/%dd”)
knime.in$“j_date”<- as.Date(knime.in$j_date , format = “%yyyy/%mm/%dd”)

And here is the output error message:

knime.out <- knime.in

  • knime.in$“filing_date” <- as.Date(knime.in$filing_date , format = “%yyyy/%mm/%dd”)
  • knime.in$“effdate” <- as.Date(knime.in$effdate , format = “%yyyy/%mm/%dd”)
  • knime.in$“dob” <- as.Date(knime.in$dob , format = “%yyyy/%mm/%dd”)
  • knime.in$“j_date”<- as.Date(knime.in$j_date , format = “%yyyy/%mm/%dd”)
  • #data.frame$filing_date <- as.Date(df$filing_date , format = “%yyyy/%mm/%dd”)
  • #data.frame$effdate <- as.Date(df$effdate , format = “%yyyy/%mm/%dd”)
  • #data.frame$dob <- as.Date(df$dob , format = “%yyyy/%mm/%dd”)
  • #data.frame$j_date <- as.Date(df$j_date , format = “%yyyy/%mm/%dd”)
    Error: ‘origin’ must be supplied
    Error: ‘origin’ must be supplied
    Error: ‘origin’ must be supplied

Now, I have 3 challenges, I am missing the dashes, and I don’t know if knime.in or knime.out is where I have to point things. Also, I want to name this table SQLimportPrep and when I tried to do that, I failed. I am sure it’s a minor syntax issue. The 3rd challenge is having to move this data into a SQL database. Do I connect the RSnippet directly to a DB Writer node?

Many thanks in advance for your help.
Noemi

Without looking into details first here are two examples that might help you

With data bases you would typically use a DB creator to establish the structure and then a DB loader to fill the table.

KNIME Database Extension Guide

https://docs.knime.com/2020-07/db_extension_guide/index.html

2 Likes

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! :partying_face: 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

Not sure about the new topic. Maybe right now to continue here.

Maybe you could provide us with an example of the data that demonstrates the problem of insert and a local database or information into what database you want to insert.

With databases and KNIME typically there are various settings of type mapping that are required and the defaults are not always suitable.

Hi @malauber71,
OK, I tried several of these and ended up learning that a few things.

I had to take a few steps back and fill in the null values in the table with 1/1/1900 00:00:00 using the Preset Date/Time (Legacy) node for all of my date fields. Once I got that to flow, I used the Table Writer node followed by a table reader node. I connected that reader to a DB Table Creator Node preceded by a Microsoft SQL Connector Node and Finalized with a DB Insert node. I connected the table reader output ports to the DB Table Creator and the DB Insert ports.

There were many ambiguous “write to DB errors” when I started working with the DB Insert node and found that permissions on my user account (SQL server) were not set up properly. I got that situation squared away and still struggled with errors until I entered “dbo” in the Schema field, and I unchecked the Use Dynamic Settings button, and manually added all of the columns in the Columns tab. I then connected the DB Insert node and used the same settings for schema, and table name from the previous 2 nodes. For this node, however, I only added the Output Type Mapping information for the date columns.

Word of caution for new users: if you run the DB Insert node more than once, you will have duplicates multiplied by the number of times you execute. :grinning:
Don’t let your SQL server fool you, it’s there - just needs to refresh.

In summary, these settings worked:

The Microsoft SQL Server Connector
I mapped the dates that are Source Type: Local Date Time to TIMESTAMP
DB Table Creator Node
Uncheck Use dynamic settings
Schema: dbo
Columns Tab: Mapped all of the columns in the dataset
DB Insert node
Schema: dbo
Output Type Mapping tab: mapped the dates that are Source Type: Local Date Time to TIMESTAMP

2 Likes