Issues with reading multiline rows in a csv file in ADF mapping data flows

Lionel F 26

Hi,

I'm having issues with multiline fields in a csv file.

I have enabled "use first row as header" in the source dataset. And in mapping dataflows, I have enabled the "multiline row" option in the source settings.

This setup reads the multiline rows correctly, but for some reason the last column header is lost as shown in the attached image (it should say start_date, and I can't seem to find a way to manually add a column header to it). If I disable "use first row as header", everything works correctly, but ADF then has an issue with identifying the correct data type as it assumes all columns are text because of the first row being in the body.

Has anybody come across a similar issue, if not what would be the best way to deal with this?

HarithaMaddi-MSFT 10,136 Reputation points

2021-01-06T11:31:57.457+00:00

Hi @Lionel F ,

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

I was able to reproduce the issue when using "use first row as header" along with multiline rows property. If not using first row as header, the string datatype can still be converted to date from projection window. I understand its a manual step and is not desired. I will check with Product team on the issue and will get back to you with their insights.

Stay tuned!
Lionel F 26 Reputation points

2021-01-06T15:14:06.25+00:00

Hi Haritha. Thanks for your response. I am currently using a workaround where I untick the "use first row as header", then manually update the column name again afterwards. As you said, it is not the slickest way of doing it - please do keep me updated on what the product team says!
HarithaMaddi-MSFT 10,136 Reputation points

2021-01-08T11:40:56.857+00:00

Hi @Lionel F ,

Thanks for your understanding. Product team suggested not to import schema when using "first row as header" and look at the data preview which is returning accurate results and the columns are identified as drifted. However, the type of the columns is still showing as string and I posted the query on the same to them. I am waiting for the response and will get back to you as soon as I hear on it.

Please let me know for any queries on approach suggested above for accurate data retrieval.
HarithaMaddi-MSFT 10,136 Reputation points

2021-01-12T07:22:06.427+00:00

Hi @Lionel F ,

Thanks for your patience. As mentioned above, I am seeing the string data type after skipping import schema step as suggested by product team but the team said they are able to see "date" datatype appropriately for a column. I am working with them in understanding the gap. Meanwhile, please suggest if you are able to see appropriate datatype when not importing schema with all the other options selected.

Looking forward for your findings!
HarithaMaddi-MSFT 10,136 Reputation points

2021-01-15T08:50:22.137+00:00

Hi @Lionel F ,

We have not received a response from you. Can you please share your findings using suggested approach as requested above.

Accepted answer

HarithaMaddi-MSFT 10,136 Reputation points

2021-01-18T12:49:50.12+00:00
Hi @Lionel F ,

Product team confirmed that without importing schema and using headers as an option will retrieve the data appropriately when having multiline rows in the data. The type will be detected as date appropriately and format needs to be specified if multiple date format data appears in the file.

Please let us know for further queries and we will be glad to assist.

--

Please accept an answer if correct. 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.
Please sign in to rate this answer.
HarithaMaddi-MSFT 10,136 Reputation points

2021-01-19T12:43:32.543+00:00

Hi @Lionel F ,

We have not received a response from you. Please suggest if above suggested approach is helpful. Otherwise, let us know and we will continue to engage with you on the query.

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

2021-01-20T07:12:00.803+00:00

Hi @Lionel F ,

We still have not heard back from you. Following up to check if above suggested approach is helpful. Otherwise, let us know and we will continue to engage with you on the query.

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.
Sign in to comment

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

Share via

Issues with reading multiline rows in a csv file in ADF mapping data flows

0 additional answers

Your answer