Maintaining Data Integrity while Converting Excel Table to JSON Using ADF Copy Activity

Question

Maintaining Data Integrity while Converting Excel Table to JSON Using ADF Copy Activity

Tulsi K 20

Hi, I'm currently working with an Excel file that contains data similar to the table shown in the attached screenshot. User's image

I'm using Azure Data Factory's copy activity with dynamic column mapping to transform this table into JSON format. However, when I inspect the JSON output, I notice that I'm missing the Euro symbol and commas.

The output is like below.

{"test":"0","project":"-23736751.93"} {"test":"0","project":"20326198"}

Kindly assist how can I copy the data exactly.

Dronec 181 Reputation points

2024-02-26T09:12:18.13+00:00

Euro symbol and commas do not exist in the source data, they're just a result of formatting in Excel, this is why you cannot see them in JSON.
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2024-03-12T09:10:13.32+00:00

Hi Khimlal, Tulsi ,

Just following up to see if the below answer helped. Please do consider clicking Accept Answer as accepted answers help community as well. Also, please click on Yes for the survey 'Was the answer helpful'

Accepted answer

0 additional answers

Your answer

Dronec 181 Reputation points

2024-02-26T09:12:18.13+00:00

Euro symbol and commas do not exist in the source data, they're just a result of formatting in Excel, this is why you cannot see them in JSON.
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2024-03-12T09:10:13.32+00:00

Hi Khimlal, Tulsi ,

Just following up to see if the below answer helped. Please do consider clicking Accept Answer as accepted answers help community as well. Also, please click on Yes for the survey 'Was the answer helpful'

Answer 1

AnnuKumari-MSFT 34,556 Microsoft Employee Moderator

Hi Khimlal, Tulsi , Welcome to Microsoft Q&A platform and thanks for posting your query here. From the description of your query, it seems you are trying to excel data from csv format to json using copy activity of ADF pipeline. However, the output is not coming as expected as it's missing the Euro symbols. I tried to repro your scenario from my end , however, I didn't face the same issue. The special character(Euro) as well as the comma are preserved in the output json file. As suggested above , kindly make sure the source file contains Euro symbol in the excel or not. Additionally, in the mapping tab of copy activity, ensure that no mapping is done and leave it as blank , ADF will do the auto-mapping. Hope it helps. Kindlly accept the answer by clicking on Accept answer button. In case you are still facing the same issue, then kindly share screenshot of copy activity configuration and mapping. Thankyou

Tulsi K 20 Reputation points

2024-02-26T15:42:51.83+00:00

..........
Tulsi K 20 Reputation points

2024-02-26T15:46:10.5833333+00:00

Hi @AnnuKumari-MSFT Thanks for looking into this. I am trying to excel data from Excel format to Json using copy activity of ADF pipeline. I tried with CSV seems like it is working fine. I am getting euro and comma as expected. The Issue is with excel format. Is there any limitation?
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2024-03-06T08:42:54.9066667+00:00

Khimlal, Tulsi ,

Excel format can't be properly rendered in ADF sometimes due to data encryption . However, could you please confirm what is the extension of your excel file , is it .xls? Kindly try to change it to .xlsx and try again with excel dataset and see if it works.

If it doesn't , I would recommend you to use delimited text dataset.

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou

Share via

Maintaining Data Integrity while Converting Excel Table to JSON Using ADF Copy Activity

0 additional answers

Your answer