Date Format not changing

[Comment Holder]

Hi All,

I have started using OpenRefine. When I import from Excel, no matter what the date format is, it always displays as 2009-06-20T00:00:00Z.

I need to get rid of the hours, i.e this part "T00:00:00Z"; it would be even better if I can have this date format dd-mm-yyyy or such. I tried using the Date Functions, but got no where. If I transform the date to text, it works, but then I lose the date-filter abilities.

Any assistance is highly appreciated. 

Thanks in advance,

CH

[Thad Guidry]

We have your use case already documented on our wiki's Quick Recipes section:

https://github.com/OpenRefine/OpenRefine/wiki/Recipes#change-2010-05-31t01100z-to-05312010

Let us know if anything does not make sense, or where we can make improvements in documenting this.

Thanks for using OpenRefine !

--
You received this message because you are subscribed to the Google Groups "OpenRefine" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openrefine+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

-Thad

+ThadGuidry
Thad on LinkedIn

[Comment Holder]

Hi Thad,

Many thanks for your kind assistance. OpenRefine is such a good DB manager/analyzer. I had troubles at first including 3mio+ records, but solved it later by increasing the Heaps on a comp with 64GB of RAMs, and it worked. It required around 20GB to work.

I tried what is suggested on the page, and that I couldn't locate before. When I try to transform using the value slice code, I get "ut/l./java" as a value for all fields without any syntax errors. Kindly note that I am using Java 64Bit; could this be the reason?

I would also be very thankful if you advise me whether it is possible to apply the text facet to multiple values at once. Thus, instead of including each one and waiting, I select the desired values and fetch all at once.

Thanks again, your help is much appreciated.

CH 

[Thad Guidry]

On Mon, Apr 28, 2014 at 10:08 AM, Comment Holder <commen...@gmail.com> wrote:

Hi Thad,

Many thanks for your kind assistance. OpenRefine is such a good DB manager/analyzer. I had troubles at first including 3mio+ records, but solved it later by increasing the Heaps on a comp with 64GB of RAMs, and it worked. It required around 20GB to work.

I tried what is suggested on the page, and that I couldn't locate before. When I try to transform using the value slice code, I get "ut/l./java" as a value for all fields without any syntax errors. Kindly note that I am using Java 64Bit; could this be the reason?

No, you can and should use 64bit when working with files over 1.7 GB...

Did you try the other method, by converting the date to a string format with the specified formatting that you want as a string ?  Such as...

toString(toDate(value),"dd/MM/yyyy")

 

I would also be very thankful if you advise me whether it is possible to apply the text facet to multiple values at once. Thus, instead of including each one and waiting, I select the desired values and fetch all at once.

Not sure what your asking... The text facet works on 1 column at a time... you can create multiple text facets with different expressions...and use or alter them on the left side panel,  You can combine them to perform a multi-faceted view of your data...but yes, you have to select your options in each facet panel.

[Comment Holder]

Thanks Thad,

My mistake, I thought it was clear in the first post. I already tried converting it to text, but then I lose the ability to filter with dates :(

Yes, I mean a text facet on 1 column only. Here is an example, suppose the column is the country and there are 100 countries. I want to select 10 out of them; right now, I have to select one after the other. This takes time because I have to wait for each one to fetch. I thought there could be a solution such as I highlight the ten and then filter with one-click instead of ten. Maybe through the use of value.match(.. something like this value.match(US And France And Germany...).

Thanks again for your time Thad,

CH

[Thad Guidry]

On Mon, Apr 28, 2014 at 11:01 AM, Comment Holder <commen...@gmail.com> wrote:

Thanks Thad,

My mistake, I thought it was clear in the first post. I already tried converting it to text, but then I lose the ability to filter with dates :(

It uses Java's SDF (SImplified Date Format)... you can still keep the Date formatting, and lose the hour, min, sec...

value.toDate(n,"dd-MM HH:mm:ss,S").datePart('month')

Yes, you lose the ability to filter bv dates...but you still have the ability to filter by text.. perhaps just treat your dates as strings, faceting as need be, until you get things aligned, cleaned up, etc... then convert the strings back to dates... value.toDate(n, "yyyy-MM-dd") or use the Edit Cells -> Common transforms ...

 

Yes, I mean a text facet on 1 column only. Here is an example, suppose the column is the country and there are 100 countries. I want to select 10 out of them; right now, I have to select one after the other. This takes time because I have to wait for each one to fetch. I thought there could be a solution such as I highlight the ten and then filter with one-click instead of ten. Maybe through the use of value.match(.. something like this value.match(US And France And Germany...).

We don't have a "apply" or "select" button on the facet's...they are all initialized and refreshed upon a change event.

We could change the handling of that, to support an "apply" or "select" event...feel free to open an issue for this feature enhancement and post a bounty if you feel it is a must have for you.

Thanks again for your time Thad,

CH

--

You received this message because you are subscribed to the Google Groups "OpenRefine" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openrefine+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Comment Holder]

Hi again Thad,

The datePart works but would it be possible to concatenate the month with the day and year?

Thanks for the other answer, well clear. I strongly believe that OpenRefine is an outstanding project. I don't really use the Refine part, as I have already developed my algorithms with VBA. Thus, although that was the main purpose, it really does work as an extremely useful data analysis program. I will certainly open this issue as I believe it will certainly add great value to the project. I would also like to hint out that with about 3 million rows and 50 columns, the project is working fine except that it is a little bit slow and consumes too much memory (about 18 GB when it is opened).

Please note that in case I need to reply, I won't be able to do it before the morning.

Thanks again & all the best,

CH 

 

[Tom Morris]

Refine only has a DateTime type (which we call Date).  There is no date type that has no time.  There is also no ability to format dates (or numbers) for display.  We use a default format for everything.

What we do offer, as Thad outlined, is the ability to format things when being converted to a string or when being exported (not sure he mentioned the latter capability).

As an aside, if you're doing any date processing using Excel, you're already in trouble:

http://compoundthinking.com/blog/index.php/2006/01/20/excel-date-wierdness/

Tom

 

[Comment Holder]

Dear Tom,

Thanks for the input. I truly believe that OpenRefine is extremely powerful, and hope that one day, it won't be number one in Refining but in analysis too.

Regarding Excel Weirdness; personally I don't face problems. Regarding the date issue mentioned on the linked page, I faced it once and it was solved in couple of minutes because many people had faced the problem especially when you receive files created/edited with Mac or so (the 1900/1904 issue). I never keep any links or formulas in any file. The last couple of lines in any script or part of script I write would be copy/paste values..etc. I just use it as a data handler, but to give the relevant credit, with the use of VBA, it is indeed very powerful. It is just I am facing the problem to put all the records in one table.

Sorry for writing such a long paragraph, but had to clarify the point. Anyways, I thank you, thank Thad, and of course everyone contributing to this wonderful project.

Among the textual algorithms I have developed in VBA, there is one for matching records based on defined criteria in a long text string irrespective how long or short it is. I hope that maybe one day, I will contribute to OpenRefine.

All The best+
Ali

[Thad Guidry]

Ali,

FYI, the R Lang guys really want to move into using OpenRefine as a viewing tool for analysis against R's output... but that requires a lot more contributors to OpenRefine to make that happen.

It is a doable extension, but requires re-working the way that OpenRefine stores it data layers.  And we haven't tackled that issue yet.  So most R Lang users, just import/export all day long in and out of OpenRefine.

GREL has a fingerprint() command that you might be interested in for matching long text strings, among other helpful commands...that do string approximation.

--

You received this message because you are subscribed to the Google Groups "OpenRefine" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openrefine+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.