ODK aggregate french computer settings saves start dates as general format in a CSV file

Nefisco_Gdg · October 25, 2016, 11:07am

Dear Group memmbers

I have a problem with exporting my files in ODK Aggregate to a CSV file
I am using a French configured computer and then my starttime, submission data, etc, are stored as a standard format. and do not link with my database any more.

SubmissionDate
24 oct. 2016 10:35:16
24 oct. 2016 10:43:57
24 oct. 2016 10:42:22
23 oct. 2016 11:00:45
23 oct. 2016 11:00:43

If I use ODK aggregate on an English configured computer, I get date files
SubmissionDate, which I want to have
24/10/2016 12:35
24/10/2016 12:43
24/10/2016 12:42
23/10/2016 13:00
23/10/2016 13:00

I have done this before from French configured computers and it always went well.

Any help is welcome

kind regards
Gertjan de Graaf

yanokwa · November 2, 2016, 5:17pm

Hi Gertjan,

What operating system (Windows/Linux/Mac), version of Tomcat and
MySQL/PostgreSQL are you using? Do you get the same behavior when you
use the Aggregate website or Briefcase?

Thank,

Yaw

···

On Tue, Oct 25, 2016 at 12:07 PM, wrote: > Dear Group memmbers > > I have a problem with exporting my files in ODK Aggregate to a CSV file > I am using a French configured computer and then my starttime, submission data, etc, are stored as a standard format. and do not link with my database any more. > > SubmissionDate > 24 oct. 2016 10:35:16 > 24 oct. 2016 10:43:57 > 24 oct. 2016 10:42:22 > 23 oct. 2016 11:00:45 > 23 oct. 2016 11:00:43 > > > If I use ODK aggregate on an English configured computer, I get date files > SubmissionDate, which I want to have > 24/10/2016 12:35 > 24/10/2016 12:43 > 24/10/2016 12:42 > 23/10/2016 13:00 > 23/10/2016 13:00 > > I have done this before from French configured computers and it always went well. > > Any help is welcome > > kind regards > Gertjan de Graaf > > -- > -- > Post: opendatakit@googlegroups.com > Unsubscribe: opendatakit+unsubscribe@googlegroups.com > Options: http://groups.google.com/group/opendatakit?hl=en > > --- > You received this message because you are subscribed to the Google Groups "ODK Community" group. > To unsubscribe from this group and stop receiving emails from it, send an email to opendatakit+unsubscribe@googlegroups.com. > For more options, visit https://groups.google.com/d/optout.

Nefisco_Gdg · November 9, 2016, 1:41pm

Dear Yaw
I am using windows 7
We have the server in the cloud
and I download the CSV files with ODBriecase

···

On Wednesday, 2 November 2016 19:18:11 UTC+2, Yaw Anokwa wrote: > Hi Gertjan, > > What operating system (Windows/Linux/Mac), version of Tomcat and > MySQL/PostgreSQL are you using? Do you get the same behavior when you > use the Aggregate website or Briefcase? > > Thank, > > Yaw > > On Tue, Oct 25, 2016 at 12:07 PM, wrote: > > Dear Group memmbers > > > > I have a problem with exporting my files in ODK Aggregate to a CSV file > > I am using a French configured computer and then my starttime, submission data, etc, are stored as a standard format. and do not link with my database any more. > > > > SubmissionDate > > 24 oct. 2016 10:35:16 > > 24 oct. 2016 10:43:57 > > 24 oct. 2016 10:42:22 > > 23 oct. 2016 11:00:45 > > 23 oct. 2016 11:00:43 > > > > > > If I use ODK aggregate on an English configured computer, I get date files > > SubmissionDate, which I want to have > > 24/10/2016 12:35 > > 24/10/2016 12:43 > > 24/10/2016 12:42 > > 23/10/2016 13:00 > > 23/10/2016 13:00 > > > > I have done this before from French configured computers and it always went well. > > > > Any help is welcome > > > > kind regards > > Gertjan de Graaf > > > > -- > > -- > > Post: opendatakit@googlegroups.com > > Unsubscribe: opendatakit+unsubscribe@googlegroups.com > > Options: http://groups.google.com/group/opendatakit?hl=en > > > > --- > > You received this message because you are subscribed to the Google Groups "ODK Community" group. > > To unsubscribe from this group and stop receiving emails from it, send an email to opendatakit+unsubscribe@googlegroups.com. > > For more options, visit https://groups.google.com/d/optout.

yanokwa · November 10, 2016, 11:51am

Hi Gertjan,

Export to CSV directly from the server and open the CSV in a text
editor (e.g., Notepad++), do you get the English date formatting?

Now do the same with the CSV you get from Briefcase. Is the behavior the same?

Yaw

···

On Wed, Nov 9, 2016 at 1:41 PM, wrote: > Dear Yaw > I am using windows 7 > We have the server in the cloud > and I download the CSV files with ODBriecase > > > On Wednesday, 2 November 2016 19:18:11 UTC+2, Yaw Anokwa wrote: >> Hi Gertjan, >> >> What operating system (Windows/Linux/Mac), version of Tomcat and >> MySQL/PostgreSQL are you using? Do you get the same behavior when you >> use the Aggregate website or Briefcase? >> >> Thank, >> >> Yaw >> >> On Tue, Oct 25, 2016 at 12:07 PM, wrote: >> > Dear Group memmbers >> > >> > I have a problem with exporting my files in ODK Aggregate to a CSV file >> > I am using a French configured computer and then my starttime, submission data, etc, are stored as a standard format. and do not link with my database any more. >> > >> > SubmissionDate >> > 24 oct. 2016 10:35:16 >> > 24 oct. 2016 10:43:57 >> > 24 oct. 2016 10:42:22 >> > 23 oct. 2016 11:00:45 >> > 23 oct. 2016 11:00:43 >> > >> > >> > If I use ODK aggregate on an English configured computer, I get date files >> > SubmissionDate, which I want to have >> > 24/10/2016 12:35 >> > 24/10/2016 12:43 >> > 24/10/2016 12:42 >> > 23/10/2016 13:00 >> > 23/10/2016 13:00 >> > >> > I have done this before from French configured computers and it always went well. >> > >> > Any help is welcome >> > >> > kind regards >> > Gertjan de Graaf >> > >> > -- >> > -- >> > Post: opendatakit@googlegroups.com >> > Unsubscribe: opendatakit+unsubscribe@googlegroups.com >> > Options: http://groups.google.com/group/opendatakit?hl=en >> > >> > --- >> > You received this message because you are subscribed to the Google Groups "ODK Community" group. >> > To unsubscribe from this group and stop receiving emails from it, send an email to opendatakit+unsubscribe@googlegroups.com. >> > For more options, visit https://groups.google.com/d/optout. > > -- > -- > Post: opendatakit@googlegroups.com > Unsubscribe: opendatakit+unsubscribe@googlegroups.com > Options: http://groups.google.com/group/opendatakit?hl=en > > --- > You received this message because you are subscribed to the Google Groups "ODK Community" group. > To unsubscribe from this group and stop receiving emails from it, send an email to opendatakit+unsubscribe@googlegroups.com. > For more options, visit https://groups.google.com/d/optout.

Mitch_S · November 10, 2016, 5:38pm

ODK Briefcase will export to CSV in whatever language format your computer
is configured to use (e.g., french) and in time zone of your computer. The
expectation is that whatever spreadsheet you are importing the .csv into
will then do a localized conversion of dates to their internal format.
i.e.,
https://github.com/opendatakit/briefcase/blob/master/src/org/opendatakit/briefcase/util/ExportToCsv.java#L357
and its use of
http://docs.oracle.com/javase/7/docs/api/java/text/DateFormat.html

ODK Aggregate, when using Export to CSV, will also export in whatever the
locale is configured for via the Date.toString() function:

github.com

getodk/aggregate/blob/master/src/main/java/org/opendatakit/aggregate/format/element/BasicElementFormatter.java#L193


/*
 * Copyright (C) 2010 University of Washington
 * Copyright (C) 2018 Nafundi
 *
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not
 * use this file except in compliance with the License. You may obtain a copy of
 * the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 */
package org.opendatakit.aggregate.format.element;

import static java.lang.String.join;
import static java.time.format.FormatStyle.MEDIUM;
import static java.util.Collections.emptyList;
import static java.util.stream.Collectors.joining;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Optional;
import org.opendatakit.aggregate.datamodel.FormElementModel;
import org.opendatakit.aggregate.format.Row;
import org.opendatakit.aggregate.submission.SubmissionKey;
import org.opendatakit.aggregate.submission.SubmissionRepeat;
import org.opendatakit.aggregate.submission.type.BlobSubmissionType;
import org.opendatakit.aggregate.submission.type.GeoPoint;
import org.opendatakit.aggregate.submission.type.jr.JRTemporal;
import org.opendatakit.common.persistence.WrappedBigDecimal;
import org.opendatakit.common.persistence.exception.ODKDatastoreException;
import org.opendatakit.common.web.CallingContext;

public class BasicElementFormatter implements ElementFormatter {

  private boolean separateCoordinates;
  private boolean includeAltitude;
  private boolean includeAccuracy;

  public BasicElementFormatter(boolean separateCoordinates, boolean includeAltitude, boolean includeAccuracy) {
    this.separateCoordinates = separateCoordinates;
    this.includeAltitude = includeAltitude;
    this.includeAccuracy = includeAccuracy;
  }

  @Override
  public void formatUid(String value, String propertyName, Row row) {
    row.addFormattedValue(Optional.ofNullable(value).orElse(null));
  }

  @Override
  public void formatBinary(BlobSubmissionType value, FormElementModel element, String ordinalValue, Row row, CallingContext cc) throws ODKDatastoreException {
    row.addFormattedValue(Optional.ofNullable(value)
        .map(BlobSubmissionType::getValue)
        .map(SubmissionKey::toString)
        .orElse(null));
  }

  @Override
  public void formatBoolean(Boolean value, FormElementModel element, String ordinalValue, Row row) {
    row.addFormattedValue(Optional.ofNullable(value)
        .map(Object::toString)
        .orElse(null));
  }

  @Override
  public void formatChoices(List<String> values, FormElementModel element, String ordinalValue, Row row) {
    row.addFormattedValue(join(" ", Optional.ofNullable(values).orElse(emptyList())));
  }

  @Override
  public void formatDateTime(Date value, FormElementModel element, String ordinalValue, Row row) {
    row.addFormattedValue(Optional.ofNullable(value)
        .map(JRTemporal::dateTime)
        .map(jrt -> jrt.humanFormat(MEDIUM))
        .orElse(null));
  }

  @Override
  public void formatDecimal(WrappedBigDecimal value, FormElementModel element, String ordinalValue, Row row) {
    row.addFormattedValue(Optional.ofNullable(value)
        .map(WrappedBigDecimal::toString)
        .orElse(null));
  }

  @Override
  public void formatJRDate(JRTemporal value, FormElementModel element, String ordinalValue, Row row) {
    row.addFormattedValue(Optional.ofNullable(value)
        .map(JRTemporal::humanFormat)
        .orElse(null));
  }

  @Override
  public void formatJRTime(JRTemporal value, FormElementModel element, String ordinalValue, Row row) {
    row.addFormattedValue(Optional.ofNullable(value)
        .map(JRTemporal::humanFormat)
        .orElse(null));
  }

  @Override
  public void formatJRDateTime(JRTemporal value, FormElementModel element, String ordinalValue, Row row) {
    row.addFormattedValue(Optional.ofNullable(value)
        .map(JRTemporal::humanFormat)
        .orElse(null));
  }

  @Override
  public void formatGeoPoint(GeoPoint value, FormElementModel element, String ordinalValue, Row row) {
    if (value == null) {
      row.addFormattedValue(null);
      return;
    }

    Optional<String> maybeLatitude = Optional.ofNullable(value).map(GeoPoint::getLatitude).map(WrappedBigDecimal::toString);
    Optional<String> maybeLongitude = Optional.ofNullable(value).map(GeoPoint::getLongitude).map(WrappedBigDecimal::toString);
    Optional<String> maybeAltitude = Optional.ofNullable(value).map(GeoPoint::getAltitude).map(WrappedBigDecimal::toString);
    Optional<String> maybeAccuracy = Optional.ofNullable(value).map(GeoPoint::getAccuracy).map(WrappedBigDecimal::toString);

    if (separateCoordinates) {
      row.addFormattedValue(maybeLatitude.orElse(null));
      row.addFormattedValue(maybeLongitude.orElse(null));
      if (includeAltitude)
        row.addFormattedValue(maybeAltitude.orElse(null));
      if (includeAccuracy)
        row.addFormattedValue(maybeAccuracy.orElse(null));
      return;
    }

    if (maybeLongitude.isPresent() && maybeLatitude.isPresent()) {
      List<String> parts = new ArrayList<>();
      parts.add(maybeLatitude.orElse(""));
      parts.add(maybeLongitude.orElse(""));
      if (includeAltitude && maybeAltitude.isPresent())
        parts.add(maybeAltitude.orElse(""));
      if (includeAccuracy && maybeAccuracy.isPresent())
        parts.add(maybeAccuracy.orElse(""));
      row.addFormattedValue(String.join(", ", parts));
    }

  }

  @Override
  public void formatLong(Long value, FormElementModel element, String ordinalValue, Row row) {
    row.addFormattedValue(Optional.ofNullable(value)
        .map(Object::toString)
        .orElse(null));
  }

  @Override
  public void formatString(String value, FormElementModel element, String ordinalValue, Row row) {
    row.addFormattedValue(Optional.ofNullable(value)
        .map(Object::toString)
        .orElse(null));
  }

  @Override
  public void formatRepeats(SubmissionRepeat repeat, FormElementModel repeatElement, Row row, CallingContext cc) {
    row.addFormattedValue(Optional.ofNullable(repeat)
        .map(SubmissionRepeat::getUniqueKeyStr)
        .orElse(null));
  }
}

This file has been truncated. show original

If that is not desired, you can set the locale of your Tomcat webserver
independently of your host via

Note also that the time zone of the exported files will be the time zone of
the server; there are different steps to change that:

And the pull of data down to your computer from ODK Aggregate using ODK
Briefcase will pull and store the data in UTC time zone, so as to prevent
any potential date-time conversion issues during that transmission.

Mitch

···

On Thu, Nov 10, 2016 at 3:51 AM, Yaw Anokwa wrote:

Hi Gertjan,

Export to CSV directly from the server and open the CSV in a text
editor (e.g., Notepad++), do you get the English date formatting?

Now do the same with the CSV you get from Briefcase. Is the behavior the
same?

Yaw

On Wed, Nov 9, 2016 at 1:41 PM, nefisco.gdg@gmail.com wrote:

Dear Yaw
I am using windows 7
We have the server in the cloud
and I download the CSV files with ODBriecase

On Wednesday, 2 November 2016 19:18:11 UTC+2, Yaw Anokwa wrote:

Hi Gertjan,

What operating system (Windows/Linux/Mac), version of Tomcat and
MySQL/PostgreSQL are you using? Do you get the same behavior when you
use the Aggregate website or Briefcase?

Thank,

Yaw

On Tue, Oct 25, 2016 at 12:07 PM, nefisco.gdg@gmail.com wrote:

Dear Group memmbers

I have a problem with exporting my files in ODK Aggregate to a CSV
file

I am using a French configured computer and then my starttime,
submission data, etc, are stored as a standard format. and do not link with
my database any more.

SubmissionDate
24 oct. 2016 10:35:16
24 oct. 2016 10:43:57
24 oct. 2016 10:42:22
23 oct. 2016 11:00:45
23 oct. 2016 11:00:43

If I use ODK aggregate on an English configured computer, I get date
files

SubmissionDate, which I want to have
24/10/2016 12:35
24/10/2016 12:43
24/10/2016 12:42
23/10/2016 13:00
23/10/2016 13:00

I have done this before from French configured computers and it
always went well.

Any help is welcome

kind regards
Gertjan de Graaf

--

Post: opendatakit@googlegroups.com
Unsubscribe: opendatakit+unsubscribe@googlegroups.com
Options: http://groups.google.com/group/opendatakit?hl=en

You received this message because you are subscribed to the Google
Groups "ODK Community" group.

To unsubscribe from this group and stop receiving emails from it,
send an email to opendatakit+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Post: opendatakit@googlegroups.com
Unsubscribe: opendatakit+unsubscribe@googlegroups.com
Options: http://groups.google.com/group/opendatakit?hl=en

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

--

Post: opendatakit@googlegroups.com
Unsubscribe: opendatakit+unsubscribe@googlegroups.com
Options: http://groups.google.com/group/opendatakit?hl=en

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

--
Mitch Sundt
Software Engineer
University of Washington
mitchellsundt@gmail.com