Hi Guys, I’m Sabino from Italy and I’m become crazy to solve one issue but I not understand if because I’m a young EDT user, or there’s a bug.
Despite the manual setting in the output file of a column whose format I want to be “date”, opening the output file, the format remain text.
Now, I will try to explain you all steps done.
The input file is an xlsx, and the data filled in the column was shown from EDT as below…(I suppose because EDT identify date type in the ISO format?? Maybe…):
If I’m not in wrong, different from Power Query, in EDT I cannot modify the data type, but I can set them manually in the output xlsx file:
In order to solve this issue I tried also to add a new transform phase before output, the DateTime Format, but the situation didn’t changed, and I have some dubts and errors:
in the “format from” filed, the date type isn’t automatically identified from EDT? If not, I have to set the format according to how EDT shows me this data, or should I open the source file and see how it is shown to me?
in the “format to field” I setted the date format I preferred…Even if is ISO format as shown me from EDT…
In the warning sheet, there are some errors…possible that EDT not identified the informations filled in this column as date?
I hope I have clearly explained my problem, and greeting you all warmly, I remain available to provide the files I’m working on.
in Easy Data Transform, everything is basically text. If you read in an Excel column formatted as dates, they will be converted to text.
But some columns will be treated differently in some transforms if Easy Data Transform infers that they represent dates, numbers or booleans.
For example the Sort transform will sample the data for the columns it is sorting on to see what types it thinks they are. If it thinks a columns is dates, then it will sort it as dates.
Other transforms, such as Replace treat everything as text.
This approach is very flexible and avoids the hassle of having to explicitly set the type of every column. But you need to make sure all the values in a column fit one of the date formats in the Preferences window.
When you output to an Excel file, you can explicitly set the Excel format type of each column.
Your Format from is d/MM/yyyy, but the existing dates are not in that format, hence the errors. They are already in yyyy-MM-dd. So Easy Data Transform should treat that column as dates in transforms that care about dates.
Good, now I understood how EDT manage the input data.
In my prefereces window are included all default date sets and I added on the top the one with which I’m usually working: the yyyy-MM-dd.
I setted local preferences like this:
For numbers I prefer mantain comma for decimals
For the date my aspected software action is that, if input date not modified, when they were output will be managed as local preferences setted, otherwise I want/can modify the format as I want, thank’s "DateTime Format " function.
…but question in the subject of this post remain…
Why, if in my input column date the only ones information filled are in the format yyyy-MM-dd,
and in the manual output file setting I setted date for the output file,
oooook…your answers are invaluable for understanding how EDT works and the fog in my mind is starting to clear…Maybe in the EDT v.2 could parse by field and not by column.
As simulation shown in your screenshot, I also tried to found on the excel file, some data filled in a different way from yyyy-MM-dd, but I didn’t found nothing.
EDT also seems don’t found particular different values, infact when I try to transform yyyy-MM-dd to dd/MM/yyyy the transformation completes successfully, with no warning informations.
There is a transform phase I can use to search and clean from this column values different from yyyy-MM-dd?
The problem is that when I open the output excel file, even if I will change by excel, the column format value from text to date, the information will appear wrongly like this:
I had a dig through the code. The issue is that Easy Data Transform expects 90 out of the first 100 values in a column to be valid dates (not blank) for the column to count as a date column. In your data the first 100 values have 75 dates and 25 blanks. This is a bit arbitary and I will have a think about how we can do better.
As a workaround, are you able to sort on the date column so that valid dates are at the top?
Now before output to file, I added a sort organizing the date discending, and finally in the output xls file now the column is date formatting as requested in the manual formatting option.
Only thank to you, we discovered what’s happen, I hope this date management will be improved because sort my output file in this way, only just to get around the problem, is not helpfull for my job.
Last question from my side about dates:
If understand well, even if I can modify dates in a lot of different format thank’s “DateTime Format”, in order make calculations, the only one date format I can obtain in the output file, will be the one setted in the local preferences? In my case dd/MM/yyyy?
If yes, in your product update roadmap, do you think the management of this function will be implemented?
Thank you a lot for your time.
Your customer support should be an example for other realities in terms of efficiency.
Currently we don’t have an option to set a particular date format in the Excel file. We output it as a date to Excel in default date format. It is then up to Excel how it displays it. We might add some more formatting options in v2.
and I’m become crazy to solve one issue 
but I not understand if because I’m a young EDT user, or there’s a bug.
?
