I wanna export my modelling results either to an Excel file or a simple CSV in clean format.
Veda-BE tells me that the maximum row count of Excel files would be 65,536. This however is only true for old binary files, which have been deprecated since Excel 2007. Is there a way to change this behaviour?
If not, is there any option to export the data as simple comma separated values (csv) directly from the Backend?
So far, I'm exporting the results according to the concept of tidy-data-format with the text-file exporter which I am reading with Python again for doing plots etc.
However (most probably due to all the '------' lines in between), in a long-term test case (2015-2100 yearly, 20+ regions) with lots of data, I ran into memory issues.
In fact, there is no real system memory bottleneck (I used a machine with 256 GB of RAM, which was used <5%), but a software related bottleneck.
Therefore, I'd really appreciate it if one could export a simple CSV file in a future version of VEDA-BE.
thank you for providing this version of VEDA-BE with CSV export option.
However, this version seems to be buggy, as one cannot work in the Veda Tables window, since everything is blocked by a giant font-size (probably the data fields), please see screenshot attached.
Despite the bug, I (blindly) clicked the export button and noticed a two problems in the generated CSV file:
The decimal operator is a comma (,) instead of a dot (.) which will cause import problems since the comma is already used as a separator
As standard setting I'd suggest to use the dot, since VEDA-FE requires this as well.
A user choice in the VEDA-BE settings for separator, decimal value and (maybe) thousand separator, would maybe useful for some users.
All values are enclosed by double-quotes (") whereas RFC 4180 section 5 does not enforce this.
Since these double-quotes blow up the file-size enormously in bigger datasets (more than 25% in my test case), I would suggest to only enclose into double-quotes if absolutely needed (which should never be the case in VEDA since no double-quotes or CRLFs do appear in the data fields, see RFC 4180 sections 6 and 7).
Caveats: I know that there is no binding standard for CSV-files, but for ease of use, I would suggest to stick to the non-binding suggestions.
thank you for providing this version of VEDA-BE with CSV export option.
However, this version seems to be buggy, as one cannot work in the Veda Tables window, since everything is blocked by a giant font-size (probably the data fields), please see screenshot attached.
Despite the bug, I (blindly) clicked the export button and noticed a two problems in the generated CSV file:
The decimal operator is a comma (,) instead of a dot (.) which will cause import problems since the comma is already used as a separator
As standard setting I'd suggest to use the dot, since VEDA-FE requires this as well.
A user choice in the VEDA-BE settings for separator, decimal value and (maybe) thousand separator, would maybe useful for some users.
All values are enclosed by double-quotes (") whereas RFC 4180 section 5 does not enforce this.
Since these double-quotes blow up the file-size enormously in bigger datasets (more than 25% in my test case), I would suggest to only enclose into double-quotes if absolutely needed (which should never be the case in VEDA since no double-quotes or CRLFs do appear in the data fields, see RFC 4180 sections 6 and 7).
Caveats: I know that there is no binding standard for CSV-files, but for ease of use, I would suggest to stick to the non-binding suggestions.
Thanks again and regards!
Fabian
Hi Fabian,
Decimal separator is not decided by the VEDA, It is fetched from system settings. but yes we can provide an option to disable double quotes in csv file.
Is scenario import working fine in new updated VEDA-BE?
And we never seen this type of giant font size issue in VEDA-BE browser. Is other VEDA-BE windows showing text properly?
(14-06-2017, 05:39 PM)AKanudia Wrote: Let me know if you see "export to CSV" option in this version. It is the last among export options on the menu that appears with data cubes.
I tried the new version, but I am not seeing the CSV option on the export menu that appears with data cubes.
Is it supposed to be on that menu? See the screen capture below:
But I found it appearing as a button on the Toolbar at the top. I tried it and got a CSV file.
However, in the file, all the numerical values were quoted too, which is not sensible. Numerical values should not be treated as "strings". As the decimal representation of numerical values never has commas when using VEDA-BE (I believe VEDA-BE requires that the decimal symbol is period), there is absolute no reason to quote any numerical values. Moreover, in locales where the comma is used as a decimal separator, semicolon is usually used instead of comma as the field separator, and so the values will never need to be quoted.
I also agree with Fabian that none of the fields should enclosed in quotes unless absolutely needed (comma in some labels).
This feature was made very quickly, to support my own exploration of PowerBI. This is a good opportunity to refine it.
I propose to:
1. introduce a user option to use quotes or not in CSV export
2. change the delimiter to <;>
3. not have quotes around the value field in any case
4. include the CSV option in the main list, so that it can be used in batch mode too.
Note that the format of CSV output is independent of the cube layout. But the columns that are exported will depend on display settings of fields. For example, if you choose to see descriptions for a dimension, then you will see three columns for it in CSV - code, description and code+description. The extra columns can be eliminated too, to keep the size in check.
20-06-2017, 03:28 PM (This post was last modified: 20-06-2017, 05:10 PM by fg.)
(20-06-2017, 03:03 PM)AKanudia Wrote: I propose to:
1. introduce a user option to use quotes or not in CSV export
2. change the delimiter to <;>
3. not have quotes around the value field in any case
4. include the CSV option in the main list, so that it can be used in batch mode too.
Dear all,
I would agree to Amit's points 1, 3 and 4.
However, the standard delimiter should be fetched from the system's local settings, but the user should be given an option to override it, the same way like in Excel (see attachment).
Weird enough, today I don't see this large font error any more?! No idea, maybe my system just needed a restart...
I will tell you if or when it appears again!
Cheers!
Fabian
Edit: Sorry, missed the attachment in the first post.
However, one additional request: I can see that the value field gets dumped in the middle of other columns.
This is bad for importing the CSV file e.g. into GAMS. If the value field would be the last one, importing the CSV file would work without any problems (assuming the values are without quotes). Would that be possible?
Additionally, I noticed that no timestamp is attached to the CSVs filename, even though this option is activated in VEDA-BE's export options (it works for all other types files).
While refining this, I would like to mention that the timestamp of the point in time, when I export a from VEDA-BE is not really useful for me, as it only represents when I triggered the export button.
It would be better if the timestamp would be the one, when I last imported results from FE into BE, since this timestamp does represent one specific model run.
any new word on the topic, e.g. CSV export with quotes only where necessary, fetched delimiters from the system and attached timestamp to the CSVs filename?
Now that the latest BE update happened to become publicly available (in another thread), I was able to test VEDA-BE 4.9.912, and I can see that the options to include a timestamp in the file name, to set the delimiter, and to manually omit the quotes are there. I guess I am happy with these options in place.
However, fetching the delimiter from system settings does not seem to work: My system setting for the list delimiter is comma, but I am getting ; as the delimiter when I blank out the option in VEDA-BE. I am also not sure whether the automatic "export with quotes only where necessary" works.
