VEDA ignoring data in Excel spreadsheets when formatted in certain ways

[pedodds]

I'm using Veda-FE v4.5.4 and Office 2016.

Veda-FE ignores or truncates data in some cells in tables that have particular cell formats.  Examples:

1. "BY_Trans", worksheet "ELC Trans Tables", cells I9:I11 - custom format ignored.
   


2. "SubRES_NewELC_Trans", worksheet "Min AF", cells I9:I12 - custom format and % format both ignored.
   


3. "Scen_SCEN_RSR_FF-Price-High", worksheet "UPD", cells I52:I375 - data are truncated to 2 decimal places in Excel and are only imported to 2 decimal places in Veda-FE.
   

I'm not sure if this is a VEDA issue or an incompatibility with Excel 2016 compared to earlier versions.  All of these cells were imported correctly using an earlier version of VEDA and Office 2010.

[Antti-L]

Interesting!  Although I am not affiliated with Kanors, I have a comment, too:

I guess you mean by "custom format ignored" the opposite, that the custom format is in fact affecting the result when importing the file into VEDA-FE?

I tested the SubRES_NewELC_Trans case both with the Excel 2003 format (.xls), and with the .xlsx and .xlsb formats with Excel 2010, and with two different VEDA versions.  In all cases, the Excel 2003 format (.xls) was imported correctly, i.e. all the NCAP_AFA values were imported exactly as the numerical values in the cells, regardless of the custom display format.  However, with both the .xlsx and the .xlsb formats, the file was not imported correctly, since all the NCAP_AFA values were ignored, apparently due to the custom formats causing the values to be interpreted as text, and thus giving a blank numerical value.

Hence, it seems safer to use the good old .xls format (which I have been using myself always with VEDA).
Just use "save as" command in Excel, and choose the 2003 format, and see if that helps.

[pedodds]

Hi Antti, that is what I mean.  Thanks for your investigations, which seem to suggest that the issue is probably not caused by a changes in Office 2016 and is more likely to be a VEDA-FE issue.

It's important because previous versions of VEDA-FE imported this data correctly, so models that were verified correct might now have missing data, as our model did.  There was a suggestion in the ETSAP VEDA improvements document that we could drop .xls format and move to .xlsx, but that seems premature if .xlsx files are not being imported correctly.

I fixed these issues in the .xlsx simply by changing the format to number.

[Antti-L]

You say that "previous versions of VEDA-FE imported this data correctly".
As I said in my post, I could not see any difference between VEDA versions.  
I tested with the latest VEDA-FE version, and a version from several years ago (maybe from around 2012).
I could only see differences between importing .xls and .xlsx/.xlsb files. Are you sure about your statement that previous versions of VEDA-FE imported that data correctly in the .xlsx format?

Note that the dirver for Microsoft Microsoft Excel data connectivity is different for .xls and .xlsx, which I suspect may be causing the difference.

[pedodds]

I'm not sure.

I found these issues by comparing DD files against those produced by the UK Government using an older version of VEDA-FE but with the same .xlsx files, which did import the data in these cells correctly.

This is why I reached that conclusion. The UK Government are using Office 2010 but I don't know which version of VEDA-FE they are using.

[AKanudia]

Thanks Antti, for the digging that you have already done. We will run some tests and get back to you by end of tomorrow.

[Vikrant]

Thanks Antti, for the digging that you have already done. We will run some tests and get back to you by end of tomorrow.

Hi Paul,

Can you check for any error log in "Veda\Veda_FE\Logs" folder? or you can send me the complete error log file.

Check for #1 and #2 with testing version, and #3 will resolve if you remove "m£/PJ" from cell I51 on the UPD sheet.

We will try to handle #3 issue in next update. 

1. "BY_Trans", worksheet "ELC Trans Tables", cells I9:I11 - custom format ignored.
   
   


2. "SubRES_NewELC_Trans", worksheet "Min AF", cells I9:I12 - custom format and % format both ignored.
   
   


3. "Scen_SCEN_RSR_FF-Price-High", worksheet "UPD", cells I52:I375 - data are truncated to 2 decimal places in Excel and are only imported to 2 decimal places in Veda-FE.
   

[pedodds]

Cut-down version attached in Word format as the forum wouldn't let me upload a txt file.

Paul

[Antti-L]

Dear Vikrant,

Thanks for the test version. I tested with it, and got the 1.0% NCAP_AFA value now into VEDA-FE, while the zero values were still ignored. In addition, any other values in that column were truncated to the displayed decimals. In other words, the whole column was apparently still being treated as text, and not numeric values, even though all the values in that column were, in fact, numeric. But importing numerical data columns as text is not good, because then you may lose a lot of the accuracy in the numbers, which may be critical.

When importing in the .xls format, all the values are correctly treated as numeric values, as they should be.

Nonetheless, I also found out that if I change the formatting to "Accounting" (with no currency symbol), then all the values in the column are imported as numerical values even in the .xlsx format, despite the format displaying "-" for zeros, just like it was in Pauls original example.

[Antti-L]

Are there any further insights/enlightments about this problem?

• What would explain the fact that the UK Government, using an older version of VEDA-FE but with the same .xlsx files, were able to import the data in those templates correctly, while both Paul and me were not able to do so?
  


• Will users be able to use any number formats in their .xlsx templates, and be assured that the parameter data will be imported correctly with full numerical precision, as it is when using the .xls format templates? (Except for UPD tables using operators, which would be text columns.)
  


• If users of existing models using the .xls format would like to move into using the .xlsx format, can they be assured that the model data, when re-imported into VEDA-FE, will be the same as before?
  

[olexandr]

Interesting!  Although I am not affiliated with Kanors, I have a comment, too:

I guess you mean by "custom format ignored" the opposite, that the custom format is in fact affecting the result when importing the file into VEDA-FE?

I tested the SubRES_NewELC_Trans case both with the Excel 2003 format (.xls), and with the .xlsx and .xlsb formats with Excel 2010, and with two different VEDA versions.  In all cases, the Excel 2003 format (.xls) was imported correctly, i.e. all the NCAP_AFA values were imported exactly as the numerical values in the cells, regardless of the custom display format.  However, with both the .xlsx and the .xlsb formats, the file was not imported correctly, since all the NCAP_AFA values were ignored, apparently due to the custom formats causing the values to be interpreted as text, and thus giving a blank numerical value.

Hence, it seems safer to use the good old .xls format (which I have been using myself always with VEDA).
Just use "save as" command in Excel, and choose the 2003 format, and see if that helps.

Actually, I had exactly the opposite experience during the TIMES course this January in DTU. One of our master students had a problem that data from one of his xls files was not imported correctly. The file looked perfectly normal (very similar to those of his peers); it had custom format on some of the cell ranges, but changing that did not help. After struggling with it for a while and not finding a solution I told him to convert it to xlsx and the problem was solved.

Unfortunately, I do not remember which table coused the problem and I don't seem to have any records left about this problem.

[Vikrant]

Please check if this version resolves these issues.

VEDA already makes a temporary copy of files that support TFM tables before they are imported. In this version, the .NumberFormat of .CurrentRegion is changed to “General”.

 
Antti, please let us know if you see any downside of this approach. Should we do this only for xlsx files?

[Antti-L]

Thanks for the new test version.  I get the following error:
--------------
Version: 4.5.414
Model: TIMES LicType: VFDV
29.06.17 15:31:49
Error while VEDA-FE: Invalid use of Null In Procedure VEDA_FE.FrmGraphicView.Fill_ErrorAlert_And_Stats [264]
{0}
--------------
Otherwise, the new test version imports the test TFM_INS table correctly from .xlsx, which is as expected, if the number format is changed to "General", as you said. To me, it looks like a reasonable solution for improving the robustness, or do you see any drawbacks or performance overheads? But can you tell me what was the solution in the UK Government version of VEDA?

As to my knowledge the number formats do not affect the imports with the .xls format, I would suggest to apply the NumberFormat change only for .xlsx and .xlsb files.

[AKanudia]

Thanks for testing Antti.

We also think it is a good solution. Sure, we will restrict this format change step only to .xlsx and .xlsb files.