[SOLVED] CSV Import errors with "Value 0 for clockhourOfHalfday must be in the range [1,12]"

[mircsicz]

Hi all,

I'm trying to import energy profile's from grid station's.

My Data Source setup is as follows:

And I've created a Data Point for the Value's of electric power (kiloWatt)

But the log give's me the following error:

java.lang.ArrayIndexOutOfBoundsException: 1

And the format of the CSV is as follow's:

Zeitstempel;kW
01.01.2017 00:00:00;0
01.01.2017 00:15:00;0
01.01.2017 00:30:00;0
...
02.01.2017 05:15:00;4
02.01.2017 05:30:00;28
02.01.2017 05:45:00;36
02.01.2017 06:00:00;32
02.01.2017 06:15:00;60
02.01.2017 06:30:00;76
02.01.2017 06:45:00;88
02.01.2017 07:00:00;216
02.01.2017 07:15:00;244
02.01.2017 07:30:00;372
02.01.2017 07:45:00;452

The CSV has 35040 +- 4/8 value's. Which is a whole year: 365 * 96

The only idea I see while pasting the CSV in here is to try to replace ";" with ","... I'll do so later and report back.

[mircsicz]

File format in real CSV

02.01.2017 05:15:00,4
02.01.2017 05:30:00,28
02.01.2017 05:30:00,28
02.01.2017 05:45:00,36
02.01.2017 06:00:00,32
02.01.2017 06:15:00,60
02.01.2017 06:30:00,76
02.01.2017 06:45:00,88
02.01.2017 07:00:00,216

doesn't import neither

[phildunlap]

Hi mircsicz,

There is a file, simpleCsv.csv in Mango/web/modules/dataFile/web/CompilingGrounds/CSV that indicates the format expected by the SimpleCsvImporter example that comes in the module. Or, you can modify SimpleCsvImporter.java in that same location for the CSV format you will need to import.

[mircsicz]

Hi Phil,

thank u for the reply and the hint... Read it somewhere earlier but didn't look into it!

I've adopted the timestamp inside the java

private DateTimeFormatter dtf = DateTimeFormat.forPattern("MM/dd/yyyy hh:mm:ss");

became

private DateTimeFormatter dtf = DateTimeFormat.forPattern("dd.MM.yyyy hh:mm:ss");

Which I saved as a new file and changed inside the Data Source Settings to use that template...

And now that I'm a little step closer I see this error when trying to import:

'CSV_LP': Event from import class: org.joda.time.IllegalFieldValueException: Cannot parse "01.01.2017 00:00:00": Value 0 for clockhourOfHalfday must be in the range [1,12] 

which leave's me lost again...

I get that it doesn't like a time like this, but there's 365 of those "midnight" timestamp's in a full year's profile and so for me there's no way to change that... And I'm not enough of a java programmer to change that in org.joda.time! :-(

[mircsicz]

@mircsicz said in CSV Import errors with "java.lang.ArrayIndexOutOfBoundsException: 1":

I get that it doesn't like a time like this, but there's 365 of those "midnight" timestamp's in a full year's profile and so for me there's no way to change that... And I'm not enough of a java programmer to change that in org.joda.time! :-(

My Bad:

It's late in germany, after sitting with it for a while and reading this I looked into it once more:

private DateTimeFormatter dtf = DateTimeFormat.forPattern("dd.MM.yyyy HH:mm:ss");

"HH" makes the Pattern 24hour format compatible... So now it import's my CSV!

Thank's a lot so far, but now it limit's me to 5k value's:

Point values limited to 5001 values, please reduce the time range or adjust the rollup interval

So hopefully the final question on this topic: where do I adjust the rollup interval?

Found an answer

There's still an issue with that, but so far I'ld consider this topic solved!