good morning! when i try to change type to “giorno del pagamento” column, which need to be a date, i keep getting this error! can anybody help? i tried changing column format thru Numbers but it doesn’t work. basic date DD/MM/YYYY doesn’t work either, it only accept it in STRING format.
file is very small, only 100 rows x 58 columns, no missing values.
Hi @chiabell , and welcome to the community!
Your screenshot is difficult to read clearly, and this would be tricky to answer specifically without seeing the actual data that is being loaded.
What I would suggest though is that you leave your column for the moment as “String” data type, and inspect the output from the CSV Reader. Have a look at all the rows that it has read, and see if there is something odd about one of the dates.
You could try attaching a String to date&time node to the CSV reader itself to try the conversion as that might give some clues.
If your data is not sensitive/private (or you can provide a non-confidential sample) you could upload a workflow containing your csv reader and the csv file (stored in the data folder - which you might need to create - within your workflow’s folder) and that way it would be easier for us to assist you.
1 Like
I’d start by unselecting the columns 1 by 1 and see which one is causing the error. Then you can examine the data in the offending row(s) and see how/why the format is not being recognized.
My apologies @chiabell but when I responded earlier, for some reason I couldn’t zoom in on the screenshot, but now I have been able to, and I can see your date:
I don’t think Knime will automatically handle dates that aren’t presented in 2019-01-15 format, which is why it won’t directly handle “15 gennaio 2019”, and wouldn’t handle the “basic date” format you mentioned earlier either e.g.“15/01/2019”.
I might be wrong, but I’ve not found it to work with anything but that directly. Anything else seems to require input as String and then conversion.
I gather that the date locale for Italy has lower-case months, so having brought your date in as a string, you would need to pass it to a String to Date&Time node, to convert it to an actual Date
Provided all of your dates are in the same format, this is all you should need to do.
hope that helps!
As a side note for anybody else reading in future, you may have to adjust this according to your own locale, and the exact format of dates received. For example, if the date had been using UK locale and arrived as 15 january 2019, an extra step would have been required, which would be to capitalize the month to “January”. It’s not a Knime specific thing. Unfortunately (and frustratingly) the java date format masks that Knime uses are sensitive to case of the letters, and Java does not play nicely with months written in any case other than the specific one for the locale. This is irritating, and such a waste of time for everybody! 
4 Likes
Another comment about the data, why is there a space after each comma? Is the field separator a comma (",") or a comma + space (", ")?
Thank you so so so much! i kinda knew what the problem was deep in my heart, but had no clue on how to solve it!
2 Likes
i exported in csv from numbers and then autodetected from CSV reader, column delimiter is “;”. i guess the ", " was just regular punctuation for the list.
Hi @chiabell , the reason why I asked about the delimiter is because the space could affect the data.
For example, “2019-01-15” would actually be read as " 2019-01-15", which would be a string type instead of date type. Similarly, all the integers that are in that screenshot would be prefixed by a space and be read as string.
Hi @bruno29a, I had wondered similarly but I think what @chiabell is saying is that the file doesn’t contain the space separated commas shown in the screenshot. I just gave it a try and set up some data that would fail transformation in csv reader in the same way,.and I made it ; separated values:
But the CSV Reader preview shows it like this:
Clearly something we’ll need to keep in mind, as it doesn’t help us when we’re trying to help…
2 Likes
Ahhhh, I see @takbb , it looks like it’s a display thing from the Preview. Thanks for the test, I was too lazy to test myself lol.
2 Likes
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.