So something strange has happened on the way to success. This is for a professional version of easymorph
I have project that reads from a RESTFUL API and gets information. Three of the columns are date fields. In all the UI screens of easymorph the date is shown as
2023-06-12 10:15:11 AM
so all is good.
The last step puts into a csv file. However the date is converted into this weird decimal number
45090.8313888889
I tried several different things to get this to work. Convert text to date, NOPE,
leave as text NOPE
Preserve formatting NOPE
So I was losing hope. For the client success I said, instead of a CSV, I went to excel format xlsx. Worked perfectly. Downside, is I had to change the downstream ingest into their Trucking management system.
I was also stubborn so I copied the project to may free desktop version I used for testing on my laptop and guess what it worked perfectly. No loses of date formatting.
Is this somehow related to a regional setting or some other element. Laptop is windows 10 and client paid version is windows server 2019.
Has anyone else seen this unusual behavior with dates and CSV?
In EasyMorph, like in Excel, dates are numbers. Technically, they are called OADates. Numbers and dates in EasyMorph are explained in this tutorial topic: EasyMorph | Type system and expressions
When you export dates to CSV there are two options:
- Convert dates in EasyMorph into text dates using the format() function, or
- Use the option "Preserve formatting" in the action settings. In this case, EasyMorph will export number dates in the same format as specified by the column formatting option.
I tried both of these several times. Throughout the entire UI, is appears correct, but in the final action to move to the file, the file has that weird number.
When I took the same project on my laptop it worked fine. I will keep trying but to be clear, I did exactly the two steps you suggested prior to me writing the question.
Hmm... It's technically not possible that a text column created using the format() function is still exported as a number column.
Hint: When you click a value, you can see in the bar above the workspace the cell data type. Blue means text, green is number:
Here is a sample project that generates a list of number dates, converts them to a text date, and then exports them to a CSV file.
date-output.morph (3.1 KB)
Output as seen in a text editor:
PS. Can it be that you're looking at the wrong file in the wrong folder?
I will take another shot at it. It was likely the NUT behind the wheel which is ME.
It's OK. Sometimes, it may require a few "gotchas" in the first few days of working with the application. I noticed that sometimes getting started with EasyMorph is less smooth for people who have already worked with another data preparation application and already have some established thinking patterns.
Ok, I started from scratch and it is working. Still not sure how it happened.
I do the import from web, pick my columns, rename my columns, the export to csv. Now works better and clean.
In the above post you refer to the format option. What action is that under. Still learning the platform and you have so many options. For me it is like a kid in a candy store.
Thank you for your insight. It is now working.
format() is a function that can be used in an expression. See this tutorial article on expressions: EasyMorph | Type system and expressions
