date formats?

[kenneth dyson]

Hi,
New to reCsvEditor. Trying to find a better solution to CSV manipulation than LibreOffice.
Question:
Is there any way to manipulate date formats with reCsvEditor? Like switching from yy-mm-dd to mmmm or yyyy?
Maybe with a script?
Thanks for any pushes in the right direction.

[Bruce Martin]

The only way to manipulate dates is via a script. For example here is a Groovy Script
to change the date format:

/*
        Script to change YYMMDD format dates to YYYY/MM/DD format
     for records where Qty-Sold > 0

       Current File:  RecordEditorData.file
       Current View:  RecordEditorData.selectedView
   Selected Records:  RecordEditorData.selectedView

*/

if ( RecordEditorData.view != null ) {
    for (i = RecordEditorData.view.getRowCount()-1; i >= 0; i--) {
        line = RecordEditorData.view.getLine(i);
        if (line.getFieldValue("QTY-SOLD").asLong() > 0 
        ) {
            String s = line.getFieldValue("DATE").asString();
            if (s.length() == 5) {
                s = "0" + s
            }
            line.getFieldValue("DATE")
                .set(new Date().parse("yyMMdd", s).format("yyyy/MM/dd") ); 
        }
    } 
    RecordEditorData.fireScreenChanged(true); /* when you update the screen data,
                                                 you need to notify the RecordEditor 
                                                 that the data has changed.*/
}

This Groovy script will convert the DATE Column from a YYMMDD format to a YYYYMMDD format when QTY-SOLD > 0. I am using RecordEditorData.view - current view. You can also use RecordEditorData.file - whole file and RecordEditorData.selectedView - selected Record

I used Groovy because the date format is easy. In case you do not know, the date format chars are case sensitive - it is lower-case dd and yy and upper-case YY.

There are some notes on installing Groovy into ReCsveditor / RecordEditor:

• https://sourceforge.net/p/recsveditor/wiki/Using%20Groovy%20for%20Scripting/

Scripts

For Windows scripts should be in :

• C:\Users\<UserName>.RecordEditor\reCsvEd\User\Scripts

or a sub-directory

You will find scripts are listed in the Utilities >>> Run Script Menu. The menu is only updated when ReCsvEditor is started

Script language

The script language is determined by the file extension

• js - javascript
• groovy - groovy

Script Build

On the Utilities >>> Script Build option there are various options to build java-script scripts. For the most part, they should work a groovy scripts with no / limited changes

---

You may find one of the other CsvEditors are better at date manipulation

e.g. http://csved.sjfrancke.nl/

I have not really looked at them

[kenneth dyson]

Thanks,

I will have to get more familliar with groovy scripting.

I managed to figure out how to install and put groovy in the extensions directory with linux.

At this point I will play arround with the scripting to get something going.

All the best

[kenneth dyson]

ok i am a bit stuck here. if you have a chance to have a look at this script and point me in a better direction that would be great.

the .csv has multiple columns, one is a date column (Workshop_Start_Date).

I copy that column twice and rename it so i have 3 date columns (maybe this could be automated?).

anyway, the 3 columns are:
1. Workshop_Start_Date
2. Workshop_Month
3. Workshop_Year

At this point the values are all like this yyyy-MM-dd
I would like the Month column to be MMMM (the name of the month)and the year column yyyy (the year in full).

I tried this:

/*
 * Update all records that match criteria:
 */
if ( RecordEditorData.view != null ) {
    for (i = RecordEditorData.view.getRowCount()-1; i >= 0; i--) {
        line = RecordEditorData.view.getLine(i);
        if (line.getFieldValue("Workshop_Month__c").asLong() > 0 
        )
             String month = line.getFieldValue("Workshop_Month__c").asString();
             line.getFieldValue("Workshop_Month__c")
                 .set(new Date().parse("yyyy-MM-dd", month).format("MMMM"));

         if (line.getFieldValue("Workshop_Year__c").asLong() > 0 
        )
             String year = line.getFieldValue("Workshop_Year__c").asString();
             line.getFieldValue("Workshop_Year__c")
                 .set(new Date().parse("yyyy-MM-dd", year).format("yyyy")); 
    } 
    RecordEditorData.fireScreenChanged(true); /* when you update the screen data,
                                                 you need to notify the RecordEditor 
                                                 that the data has changed.*/
}

this gives me the message:

Error: javax.script.ScriptException <eval>:9:20 Expected ; but found month
             String month = line.getFieldValue("Workshop_Month__c").asString();
                    ^ in <eval> at line number 9 at column number 20

any help would be much appreciated, i have been trying to handle this in LibreOffice, but it is hit an miss with their macros.

Thanks

[Bruce Martin]

Can you provide a sample Csv file and desired output csv, I will try writing a script based on your description

[kenneth dyson]

thanks for taking the time to check this out.
I managed to figure out how to do it in R. so no need to trouble yourself with this.
I will certainly look into ReCsv in the future. it looks like a great application.
all the best

[Bruce Martin]

Try:

if ( RecordEditorData.view != null ) {
    for (i = RecordEditorData.view.getRowCount()-1; i >= 0; i--) {
        line = RecordEditorData.view.getLine(i);
        String s = line.getFieldValue("Workshop_Start_Date").asString();
        line.getFieldValue("Workshop_Month__c")
                .set(new Date().parse("yyyy-MM-dd", s).format("MMMM") ); 
        line.getFieldValue("Workshop_Year__c")
                .set(new Date().parse("yyyy-MM-dd", s).format("yyyy") ); 

    } 
    RecordEditorData.fireScreenChanged(true); /* when you update the screen data,
                                                 you need to notify the RecordEditor 
                                                 that the data has changed.*/
}