Handling Line Breaks in Mapping Data Flow

Question

I do not have control over the input column names in an incoming Excel workbook. The column names are coming from user-entered data fields that are getting exported from another system. How can I get past the Source step when the following is true?

Error: Column name cannot contain line breakers like \n  

Okay... but this is why I'm using a Data flow to begin with... I want to clean up the data including headers with line breaks.

Answer 1

Hi @Walter Pelowski ,

Thankyou for using Microsoft Q&A platform and posting your query.

My understanding about your query is that the source dataset which is in Excel format is having columns with line breaks. You want to remove the line breaks or replace the '\n' with '' using data flow. Please let me know if that is not the case.

For this purpose , My suggestion would be to follow the below mentioned steps:

• Create dataset pointing to the Excel file and use it as the source dataset, keep the First row as Header value as unchecked and import schema.
• Use derived column transformation to replace '\n' with ' ' using the expression: replace(Column_1, '\n', ' ') for all the columns which are having line breaks
• Use Select transformation to unselect the unnecessary fields
• In Sink dataset, use csv format . Again, keep the first row as header option as unchecked.

Note: Excel as sink is not yet available in Data factory as of now. It is a read only format.

Here is the gif for your reference:

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators