Column col1 has both numeric and character data so reading it as string in excel source(xlsx). Excel data ex. col1 (General Format) DFSDF 23 12345678 Expecting to read the data as it is in azure data factory ,but its altering the numeric data and getting loaded in table as below col1 DFSDF 23.0 1.2345678E7 Its adding an extra .0 at the end of numeric data and converting it to scientific notation if the numeric value is equal to or more than 8 digits. I would like to read the raw data as it is available in excel file and then like to perform any conversion as required!!

Hi @Gandhikumar, Suganthi , Thanks for sharing more details on the requirement. I discussed with Product team and there is an existing bug for which the fix is in pipeline and will be deployed soon. We are tracking the deployment item and will update you once deployed. Workaround can be to use copy activity initially to stage the data into a csv file and use this as a source in dataflow for further transformations. This will ensure that string stores the mixed values and shows the accurate result as in excel file. Hope this helps! Please let us know for further queries and we will be glad to assist. Thanks for your patience!

Azure Data factory - Reading nvarchar column from excel source xlsx in azure data factory but numeric data is not being read properly!!

Accepted answer

HarithaMaddi-MSFT 10,136 Reputation points

2020-09-11T08:59:09.777+00:00

Hi @Gandhikumar, Suganthi ,

Thanks for sharing more details on the requirement. I discussed with Product team and there is an existing bug for which the fix is in pipeline and will be deployed soon. We are tracking the deployment item and will update you once deployed.

Workaround can be to use copy activity initially to stage the data into a csv file and use this as a source in dataflow for further transformations. This will ensure that string stores the mixed values and shows the accurate result as in excel file.

Hope this helps! Please let us know for further queries and we will be glad to assist.

Thanks for your patience!
Please sign in to rate this answer.
Gandhikumar, Suganthi 31 Reputation points

2020-09-11T14:22:54.507+00:00

Hi @HarithaMaddi-MSFT ,

Sorry the proposed workaround may not work because we faced few issues while using csv files and then decided to use excel source .When data involves both "(quotes) and comma ex. dfjj 2",fgfggh,1/4",0-160,dfggh , in csv file it displays as "dfjj 2"",fgfggh,1/4"",0-160,dfggh" and the adf divides this data and places the value in multiple columns in debug mode (another issue).To avoid this we opted for excel source and now we are facing this reported issue .

Could you please give us some ETA like when this fix will be deployed and we are not sure of the workaround as it might more time to process the data by adding more and more stages .Thanks

HarithaMaddi-MSFT 10,136 Reputation points

2020-09-14T09:00:31.9+00:00

Thanks @Gandhikumar, Suganthi for sharing additional details. For the example you shared, I tested using quotes in the excel file and marking escape character as "," in ADF and data can be read accurately as below snaps. However I understand the change in design and additional stage, time added to overall execution. We have weekly cadence in deploying changes to ADF and this fix is most likely to be deployed by the end of this month. However, the priority of fixes is dynamic and ADF product team might hold few changes depending on other fixes in the queue. I will follow up with Product team to check feasibility in prioritizing it and will update you as soon as it is deployed.

Thanks for your patience!

Gandhikumar, Suganthi 31 Reputation points

2020-09-17T15:14:41.787+00:00

Hi @HarithaMaddi-MSFT ,

Thanks for the response .Tried the solution but the data is getting divided and displayed.

and below is the csv data after uploaded in blob storage

followed the same settings ,but data is getting divided and displayed in different columns

HarithaMaddi-MSFT 10,136 Reputation points

2020-09-18T12:58:36.147+00:00

Hi @Gandhikumar, Suganthi ,

Thanks for sharing the details. Unfortunately, I am unable to reproduce the issue as shown in below GIF and attached are the JSON of pipeline and dataflow in data factory.

25785-exceltocsvpipelinejson.txt
25786-dataflowjson.txt

If these does not help, please send a mail to AzCommunity[at]microsoft[dot]com with subject: "Attn:Haritha - Azure Data factory - Reading nvarchar column from excel source xlsx in azure data factory" and we will work with you closely to reproduce the issue.

Gandhikumar, Suganthi 31 Reputation points

2020-09-21T06:46:22.653+00:00

Hi Haritha,

Thanks for the response .But the scenario which we are trying here is reading the data from csv file directly ,you have shown us converting it from excel into csv and then reading the data .
If the data is read correctly in the scenario you have shown , i assume it should work in the same way when it is read directly from csv file.

The source file screen shot which i have shown is csv file data . Although the workaround you have shown is good but would like to know why this is not working when it read directly from the csv file.

HarithaMaddi-MSFT 10,136 Reputation points

2020-09-21T15:32:04.12+00:00

Thanks @Gandhikumar, Suganthi for sharing the additional query. I see a difference in the CSV that was formed when excel was converted to CSV and the CSV you have copied above. I am attaching the snap of the CSV file generated and it works fine in this format, hope this clarifies.

Please let me know for additional queries.

Gandhikumar, Suganthi 31 Reputation points

2020-09-21T16:39:18.837+00:00

Hi @HarithaMaddi-MSFT ,

Thanks for the reply .But I could see how this is working we are adding a comma before every quote (dfjj 2,",fgfggh,1/4,",0-160,dfggh ) and its reading correctly as we are giving escape character as comma .But my input data is "dfjj 2",fgfggh,1/4",0-160,dfggh" which is giving problem when it is read directly from csv file . I would really appreciate if you could please let know if this is really working for this "dfjj 2",fgfggh,1/4",0-160,dfggh" data.

HarithaMaddi-MSFT 10,136 Reputation points

2020-09-21T18:00:23.827+00:00

Thanks @Gandhikumar, Suganthi for your patience. The fix for the original issue with the Excel is deployed and the data is appearing as intended now. Can you please check and confirm the same.

Gandhikumar, Suganthi 31 Reputation points

2020-09-22T05:34:52.06+00:00

Thanks @HarithaMaddi-MSFT ,the issue we reported in excel is resolved and we could see the data is read correctly as expected
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Azure Data factory - Reading nvarchar column from excel source xlsx in azure data factory but numeric data is not being read properly!!

0 additional answers

Your answer