Archived Forum

Unix time counting starts 1/1/1970 not 1900. So in excel, I use the formula =(((A1/60)/60)/24)+DATE(1970,1,1).

Regards the "3rd value", I think a thousands separating comma is being picked up as a CSV separator and those values should be 2071 and 1763.1. That would also explain the leading zero on the "071.00" value. If that is the case, try and find where the thousands separator is being introduced and change it there, or, as a work around for this example, concatenate the 2nd and 3rd values.

Paul

Thank You so much!

It was really simple then… sorry for the annoyance.

It works!

Anyway I think that the behavior about thousands should be corrected, since a Comma Separated Values files should have commas only to separate values, or at least should have those values delimited also by ".

Or could be used a different separator such as Tab…

But the best, to me, would be avoiding thousands marking.

Thanks again, have a nice Sunday!

If you don't like office you can use this shell script:

#!/bin/bash
INPUT=$1

#echo ${INPUT##*/}
OUTPUT=${INPUT%.*}
OUTPUT=$(echo "$OUTPUT"_converted.csv)

echo "TIME, VALUE" > $OUTPUT
conversion () {
while read LINE
do
        VALUE=$(echo $LINE|cut -d',' -f2)
        UTIME=$(echo $LINE|cut -d',' -f1)
        TIME=$(date -d @$UTIME +'%y-%m-%d %H:%M:%S')
        echo "$TIME, $VALUE" >> $OUTPUT
done < $INPUT
}

usage () {
                echo "Usage: emon_csv-conv.sh filename-to-convert.csv"
                echo ""
                echo "Use this shell script to convert CSV's exported from emonCMS to humanreada$
}

if [ $# -ne 1 ]
then
    usage
    exit 1
else
case "$INPUT" in
*.csv | *.CSV )
        conversion
        ;;
*)
        echo "it's no CSV"
        ;;
esac
fi

The time system used by EMONCMS is the Linux time system, which starts at 00:00 hours on 1/1/1970.

Thus in MS Excel you can convert the time using the following:
=(CELL+TIMEZONE_OFFSET)/(60*60*24)+"1/1/1970"
Where:
CELL contains the Linux time value
TIMEZONE_OFFSET is used if you wish to state the value in your local time zone. If you are at located in the GMT +10 hours, then you would enter (60x60x10) or 36,000.

I suggest having two columns containing this formula, then in MS Excel format one cell as date and the other as time. Alternatively use a custom cell format.

The easiest work around for the thousands separator comma in the value is to rename the file with the txt extension. Then in MS Excel open the file and select fixed width. Then create a column break before the first comma and another comma after the first comma. This will create three columns when the data is imported: time value, a comma and the value. Then you can simply delete the comma column. Thus no need to use search and replace.

Of course this won't work if the time set happens to include a value where number of digits in the time value increases.

Thanks for this thread - directly answered my question.