Problem with reading Excel file

For questions and discussion related to reading in and working with data.

Problem with reading Excel file

Unread postby PeterF » Fri Oct 08, 2021 11:50 am

Dear Tom,

I need data for gas storage levels in EU countries, which I could retrieve via a URL from an API of the data provider. Unfortunately, the file format is JSON, which RATS cannot read. I have written a small program in R, to download the data into excel files, which then I want to read into RATS for further analysis. However, I run into some difficulties. The preview in the windows explorer shows that the data is fine, including the date column and the headers for storage and capacity. I can also open the file in Excel without a problem. But the Data-Wizard does not read the date column and the headers.

Code: Select all
OPEN DATA "D:\Rats Files\Energy\France Gas Storage.xlsx"
CALENDAR(7) 2011:1:1
DATA(FORMAT=XLSX,NOLABELS,ORG=COLUMNS,TOP=2,LEFT=2,REVERSE) 2011:01:01 2021:10:06 STORAGE CAPACITY


At least with this code, RATS reads the data, but does not reverse the order. The most recent data from the file remains the first value of the series, however, it should be the last one. What is going wrong? I have the Excel file attached. BTW, when i tried to write the data from R into a comma separated file, I got error message IO19.

Best regards
PeterF
Attachments
France Gas Storage.xlsx
Data file produced with R
(82.82 KiB) Downloaded 36 times
PeterF
 
Posts: 45
Joined: Thu Apr 12, 2012 2:03 pm

Re: Problem with reading Excel file

Unread postby TomDoan » Mon Oct 18, 2021 3:46 pm

The original file (from R) uses a feature in the Excel spec which Excel itself never seems to use, which is an "inline string". I assume this was something added to the spec at some point to make it easier for 3rd party software to create modern Excel spreadsheets (the zipped XML format). (Excel itself uses the original method for handling strings which is to use a shared string table---it's a pain in the you-know-where to set up, but can dramatically reduce the size of big spreadsheets).

Offhand, I'm not seeing where we ever implemented the forced REVERSED order if the file didn't have usable dates. We'll have that and the inline strings implemented in the next maintenance release of RATS. In the meantime, I've attached a RATS format file in the proper order.
Attachments
France Gas Storage.rat
(64.25 KiB) Downloaded 26 times
TomDoan
 
Posts: 7147
Joined: Wed Nov 01, 2006 5:36 pm


Return to Data: Reading, Writing, Transforming

Who is online

Users browsing this forum: No registered users and 2 guests

cron