Hi All, I was exploring Excel as a Source in ADFv2 by trying to convert an excel file into CSV by using copy activity. I faced the below challenges: 1) There is a provision to provide Range of Excel file. How can we provide dynamic range. My excel files can have range from A3:C10 or A3:C5 or A3:C15 as the number of rows can increase or decrease in an excel file. How to define Range in such scenario. 2) In a decimal column the actual data is 0.1234534678 whereas it gets displayed as 0.1234E-05. When converting this excel file into CSV i am obtaining the displayed value rather than the actual value. I referred the discussion regarding this scenario and it was said that this scenario is resolved. https://learn.microsoft.com/en-us/answers/questions/49637/excel-to-csv-conversion-in-adf-v2.html But i am still facing the same issue. Am i missing some property?

Hi @Priya Jha , Welcome to Microsoft Q&A Platform. Thanks for posting the query. Please find below my observations 1) There is a provision to provide Range of Excel file. How can we provide dynamic range. My excel files can have range from A3:C10 or A3:C5 or A3:C15 as the number of rows can increase or decrease in an excel file. How to define Range in such scenario. ADF excel connector dynamically reads them as per number of rows in file if range is not specified. Range is optional and not mandatory. Second GIF shows the same. And if starting range is alone specified, it dynamically detects all the rows below and all the columns to the right. 2) In a decimal column the actual data is 0.1234534678 whereas it gets displayed as 0.1234E-05. When converting this excel file into CSV I am obtaining the displayed value rather than the actual value. I observed that marking the type of column as "Decimal" as below under "Mapping" tab resolved this issue. Below is snap of it. Please check and let me know if it works! Hope this helps! Please let us know for further questions and we will be glad to assist.

Range and Decimal Precision in Excel to CSV conversion in ADFv2

Accepted answer

HarithaMaddi-MSFT 10,136 Reputation points

2020-08-31T10:58:14.31+00:00

Hi @Priya Jha ,

Welcome to Microsoft Q&A Platform. Thanks for posting the query.

Please find below my observations

1) There is a provision to provide Range of Excel file. How can we provide dynamic range. My excel files can have range from A3:C10 or A3:C5 or A3:C15 as the number of rows can increase or decrease in an excel file. How to define Range in such scenario.

ADF excel connector dynamically reads them as per number of rows in file if range is not specified. Range is optional and not mandatory. Second GIF shows the same. And if starting range is alone specified, it dynamically detects all the rows below and all the columns to the right.

2) In a decimal column the actual data is 0.1234534678 whereas it gets displayed as 0.1234E-05. When converting this excel file into CSV I am obtaining the displayed value rather than the actual value.

I observed that marking the type of column as "Decimal" as below under "Mapping" tab resolved this issue. Below is snap of it. Please check and let me know if it works!

Hope this helps! Please let us know for further questions and we will be glad to assist.
Please sign in to rate this answer.
Nandan Hegde 29,911 Reputation points MVP

2020-08-31T12:15:58.543+00:00

Hi Haritha,
The 2nd scenario described above is similar to what I am facing:

https://learn.microsoft.com/en-us/answers/questions/49637/excel-to-csv-conversion-in-adf-v2.html

I have provided the detailed screenshot and explanation for the 2nd scenario of decimal value conversion.
Can you please try reciprocating that scenario and identify whether there is something which is being missed out.

HarithaMaddi-MSFT 10,136 Reputation points

2020-08-31T12:51:56.533+00:00

Hi @Nandan Hegde ,

Thanks for sharing additional details on the issue. I have observed the same issue because by default excel columns are read as string type, this can be resolved by marking them as "decimal" when mapping to sink columns as shown in the snap in above answer. This worked for me to get same example you have shared. Please let me know if it helps!

Priya Jha 866 Reputation points

2020-08-31T13:47:41.097+00:00

Hi @HarithaMaddi-MSFT ,

Thanks for your inputs.

For the 1st issue of defining range, i have excel files where data doesn't start from A1 cell. The data can start from 3rd or 4th row. Hence, i have to specify range to exclude the blank rows or irrelevant rows in my CSV file. I can define the starting range of my data as it would be constant in an file. But the ending range would be dynamic and how to define the range in this scenario.

HarithaMaddi-MSFT 10,136 Reputation points

2020-08-31T14:22:09.173+00:00

Thanks @Priya Jha for sharing additional details. One approach I found is useful for the similar scenario is to use another copy activity before the actual copy activity that loads into a temporary location(only one column can be mapped to reduce storage) and copy activity gives an output value "rowsCopied" that can be used to specify the end range as below GIF. Please suggest if this helps!

HarithaMaddi-MSFT 10,136 Reputation points

2020-09-02T06:40:52.883+00:00

Hi @Priya Jha ,

We have not received a response from you. Please suggest if above responses helped to clarify the question. Kindly let us know for further queries and we will be glad to assist.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

HarithaMaddi-MSFT 10,136 Reputation points

2020-09-03T04:41:47.2+00:00

Hi @Priya Jha ,

We still have not heard back from you. Following up to check if above responses helped to clarify the question. Kindly let us know for further queries and we will be glad to assist.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

Priya Jha 866 Reputation points

2020-09-03T07:42:08.35+00:00

Hi @HarithaMaddi-MSFT ,

Sorry for late reply.

I tried the solution you provided for Range scenario and creating dummy copy activity to get the rowsCopied data did help to solve my scenario some what.

In my excel file, i can have few rows which denotes file name or some information about the file and then the actual data starts.

When i tried converting this file to csv, using your method, the 2 blank rows got ignored in the rowsCopied data and the final csv had 2 less rows.

I can solve this by adding 2 rows while calculating the custom range : @markus.bohland@hotmail.de ('A6:D',string(add(activity('Copy data1').output.rowsCopied,2)))

But this is too much hardcoding for 2 blank rows.

Is there a better way to handle this, so that blank rows are also included in my rowsCopied data.

HarithaMaddi-MSFT 10,136 Reputation points

2020-09-04T06:57:39.027+00:00

Thanks @Priya Jha for sharing additional details. I observed the similar issue with blank rows not counted either in copy activity output or lookup activity output. I am working with internal team to understand if there is any possibility to read count of records from source file including blank rows. Also, we do not have skip lines yet in Excel Source of ADF else that would have helped implementing the actual requirement here. I will get back to you as soon as I find an alternative or once I hear update from them.

Thanks for your patience!

Priya Jha 866 Reputation points

2020-09-04T07:39:12.877+00:00

Hi @HarithaMaddi-MSFT ,

Thanks for your reply.

I stumbled upon this document: https://learn.microsoft.com/en-us/azure/data-factory/format-excel and what kind of range can be provided is mentioned here.

It is specified that we can provide only the starting cell of the excel as range in the ADF and it would automatically detect the corresponding rows and columns from that starting cell.

I tried this and it worked for my scenario.

But it would be helpful if we get skip lines and count of records including blank rows in Excel as a source.

HarithaMaddi-MSFT 10,136 Reputation points

2020-09-04T07:46:53.327+00:00

Thanks @Priya Jha for sharing here and letting me know. It got missed from me too and I have added it to answer above so that other community members can benefit from it. I would recommend you to provide ideas for skip lines and count of records including blank rows at the feedback forum. All the feedback you share, is closely monitored by the Product team and implemented in future releases.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

HarithaMaddi-MSFT 10,136 Reputation points

2020-09-07T06:26:14.207+00:00

Hi @Priya Jha ,

We still have not heard back from you. Following up to check if above responses helped to clarify the question. Kindly let us know for further queries and we will be glad to assist.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

Lakshmi Mounika Batchu 6 Reputation points

2020-10-28T11:55:28.49+00:00

Hi @HarithaMaddi-MSFT it worked for me. Thank you.
Sign in to comment

Share via

Range and Decimal Precision in Excel to CSV conversion in ADFv2

0 additional answers