Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,624 questions
Azure Data Factory cannot read '#N/A' value of Excel column
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(268.8, 50%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBO%3C/text%3E%3C/svg%3E)
Burak Ozkan
0
Reputation points
Hello,
I would like to process the Excel file and the Excel sheet has a '#N/A' value. When I try to preview data, the ADF cannot read it. Is there a way to replace that value with an empty string or null value?
Azure Data Factory
{count} votes
-
Amira Bedhiafi 33,071 Reputation points Volunteer Moderator2024-02-29T15:52:27.82+00:00 Try to use Data Flow in ADF which allows you to handle NULL values :
ifNull({ColumnName}, 'Unknown') -
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(54.400000000000006, 38%, 15%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EQ%3C/text%3E%3C/svg%3E)
QuantumCache 20,366 Reputation points Moderator2024-02-29T19:22:43.1433333+00:00 Burak Ozkan Could you please Attach the sample Excel file to the Question? Is the Data Preview looks like the below, please provide us the sample excel from your side and we will try to repro on our side to help you...! thanks for your understanding.
-
QuantumCache 20,366 Reputation points Moderator
2024-02-29T19:52:51.4533333+00:00 @Amira Bedhiafi Did you verify the Expression if that function is available? Could you please add more context from your side?
Sign in to comment
Sign in to answer