Synapse how to read excel with formula

Tamashevich, Tatsiana 40

Hello,

in Synapse spark pool we need to read excel file that contains formula inside of cells (sometimes it is simple sum, sometimes complicated formula, sometimes it is reference to another spread sheet and cell).

Unfortunately, we observe that some formulas are eveluated correctly and some are not.

For instance, if cell has formula: =SUM(A2:A10), it does not take sum value as we see it in excel, it takes only value from A2.

So far, I have tried different approached to read excel file (All of them produced same result: instead of taking value as sum of A2:A10, only value from A2 is taken):

pandas
python openpyxl
com.crealytics.spark.excel

Would really appreciate if someone can give a hint how to resolve it.

Thank you very much in advance!

Harishga 5,420 Reputation points Microsoft Vendor

2024-06-27T11:07:12.9266667+00:00

Hi @Tamashevich, Tatsiana
Welcome to Microsoft Q&A platform and thanks for posting your question here.

When working with Excel files in Synapse Spark, it’s crucial to remember that Spark doesn’t calculate formulas within the cells. It only reads the values as they were last saved. This is why you’re seeing only the value from A2 instead of the sum of A2:A10. The formulas need to be evaluated and the file saved before Spark can read the correct values.

To solve this, you should first ensure that the Excel file is saved after all the formulas have been calculated. This step updates the file with the latest values. If you’re using Python, you can automate this process with the openpyxl library. This tool allows you to open the Excel file, go through each cell with a formula, and trigger the evaluation by reassigning the cell’s value. After doing this, save the file again.

Finally, once the Excel file has all the formulas evaluated and saved, you can use Spark to read the file into a DataFrame. This way, Spark will have access to the correct, updated values from your Excel file, ensuring accurate data processing.

I hope this information helps you. Let me know if you have any further questions or concerns.
Tamashevich, Tatsiana 40 Reputation points

2024-06-28T07:32:09.5+00:00

Hi @Harishga ,

thank you for your answer.

Could you, please, clarify how to trigger the evaluation by reassigning the cell’s value?

I was trying: cell.value=cell.value, but it didn't help.

Thank you very much in advance!
Harishga 5,420 Reputation points Microsoft Vendor

2024-06-28T08:46:23.9166667+00:00
Hi @Tamashevich, Tatsiana
Assigning cell.value = cell.value doesn’t make the formula recalculate. Instead, load the workbook with data_only=False, then go through the cells with formulas and call cell.value to make them recalculate.

import openpyxl # Load the workbook wb = openpyxl.load_workbook('example.xlsx', data_only=False) # Iterate through all sheets for sheet in wb: # Iterate through all cells with formulas for row in sheet.iter_rows(values_only=False): for cell in row: if cell.data_type == 'f': # Check if the cell contains a formula # Trigger the formula evaluation by accessing cell.value _ = cell.value # Use _ to avoid assigning the value to a variable # Save the workbook to update the values wb.save('example.xlsx')

After running this code, the Excel file should have the formulas evaluated, and when you read the file into a Spark DataFrame, you should see the correct values.

Also, make sure to save the workbook after triggering the formula evaluation, as this will update the file with the latest values.

I hope this information helps you. Let me know if you have any further questions or concerns.

1 answer

Sina Salam 6,341 Reputation points

2024-06-27T11:20:50.2666667+00:00
Hello Tamashevich, Tatsiana,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

Problem

I understand that you are having issue when reading Excel files containing formulas into a Synapse Spark pool, the formulas are not being evaluated correctly. Instead of returning the calculated results of the formulas, only partial or incorrect values are retrieved, and this issue persists across different libraries and methods used for reading the Excel files.

Solution

To solve this issue, you will need to understand that not all libraries evaluate the formulas correctly, and reading Excel files with formulas in Synapse Spark pool can be tricky. So, what come to my mind are the followings:

You will need to evaluate formulas in Excel Before Reading.

Use xlwings to Evaluate Formulas. https://docs.xlwings.org/en/stable/macro.html

Use com.crealytics.spark.excel with Formula Evaluation.

Lastly, use VBA Script to Pre-evaluate Formulas. https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.saveas

Accept Answer

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

** Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful ** so that others in the community facing similar issues can easily find the solution.

Best Regards,

Sina Salam
Please sign in to rate this answer.
Tamashevich, Tatsiana 40 Reputation points

2024-06-28T07:34:04.6733333+00:00

Hi @Sina Salam ,

thank you for the detailed answer!

Could you, please, clarify how to use com.crealytics.spark.excel with Formula Evaluation?

So far, ! was not able to find the right option to be used in order to evaluate formula.

Thank you very much in advance!

Sina Salam 6,341 Reputation points

2024-06-28T12:19:38.78+00:00

Hello

Thank you for your feedback.

Regarding your question:

Could you, please, clarify how to use com.crealytics.spark.excel with Formula Evaluation?

To read Excel files containing formulas into a Synapse Spark pool and ensure proper formula evaluation, you can leverage the spark-excel library.

First, make sure you have the spark-excel library installed. You can add it to your Spark environment using the --packages command line option. For example:

For Spark compiled with Scala 2.12:

`$SPARK_HOME/bin/spark-shell --packages com.crealytics:spark-excel_2.12:<spark-version>_0.18.0` For Spark compiled with Scala 2.11: `$SPARK_HOME/bin/spark-shell --packages com.crealytics:spark-excel_2.11:<spark-version>_0.13.7`

Once the library is installed, you can create a DataFrame from an Excel file using the following Scala API:

import org.apache.spark.sql._ val spark: SparkSession = ??? val df = spark.read .format("com.crealytics.spark.excel") // Or .format("excel") for V2 implementation .option("dataAddress", "'My Sheet'!B3:C35") // Optional, default: "A1" .load("path/to/your/excel/file.xlsx")

Replace "path/to/your/excel/file.xlsx" with the actual path to your Excel file. The dataAddress option specifies the range of cells to read (e.g., 'My Sheet'!B3:C35).

By default, the library should evaluate formulas correctly. If you encounter issues, ensure that you're using the latest version of spark-excel. Starting from version 0.14.0, there are two implementations available:

Original Spark-Excel with Spark data source API 1.0.

Spark-Excel V2 with data source API V2.0+, which offers improvements and better handling of data types.

To use the V2 implementation, change your .format from .format("com.crealytics.spark.excel") to .format("excel").

References

For more reading:

GitHub - crealytics/spark-excel: A Spark plugin for reading and writing: https://github.com/crealytics/spark-excel.

spark.read excel with formula - Databricks: https://community.databricks.com/t5/data-engineering/spark-read-excel-with-formula/td-p/31948.

Best Regards,

Sina
Sign in to comment

Share via

Synapse how to read excel with formula

1 answer

Problem

Solution

Accept Answer