skip to Main Content

I use a Azure Logic app to copy excel files from a Sharepoint folder into a Datalake storage account. From here I use ADF to copy the data into an SQL DB.

Some values in the excel files have formatting on them, so for example a cell is calculated with a formula which returns 4,18128342349 but with formatting in excel the cell looks like 4.2x.

When I use my logic app the "underlying" value is copied, and not the formatted data. I.e. in the DLS the cell contains 4,18128342349. Is there any way to not take the actual value of the cell but instead take the formatted value?

Thanks in advance!

EDIT:

With ADF Copy Data Activity I’ve tried to move the xlsx file from DLS to SQL DB and the underlying values is what is copied. This is the case even if add a step where I convert the xslx files to csv before I move them to the SQL DB.

The logic app that moves the files from sharepoint to DLS:
The logic app that moves the files from sharepoint to DLS

EDIT 2:
The problem is not that the formatting is removed when I transfer the file and open it in excel. The problem occurs when I load the data into an SQL DB:

  1. Input file:

Input file

  1. Logic app as above

  2. ADF Copy Data Activity with the DLS as Source and an SQL DB as SINK.

  3. Output in the SQL DB:

Output in the SQL DB

2

Answers


  1. See: https://mikestephenson.me/2022/08/07/replicating-files-from-sharepoint-to-data-lake-with-a-logic-app/

    And: https://learn.microsoft.com/en-us/answers/questions/898508/copy-files-from-sharepoint-to-azure-blob-storage-u

    If your excel is not too complex, you can try creating an additional step to save the Excel file as a CSV file before transferring it which will keep the displayed values, not the underlying raw values.

    Login or Signup to reply.
  2. I have below value and formatted it like below:

    enter image description here

    Then used below design in Logic apps like below:

    enter image description here

    Output:

    enter image description here

    After downloading, you can see its in formatted form:

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search