question

SudhakarMani-2921 avatar image
0 Votes"
SudhakarMani-2921 asked SudhakarMani-2921 answered

Azure Data Flow - Reading CSV file multiline records

I am trying to read csv file with quoted text and this quote text has multiline character (LF - LINEFEED). When reading this file, the values after LF is considered as new record in data flow. After enabling 'multiline rows' option, the records with 1 LF value is parsed correctly. However when there is 2 LF character, the record is still broken and read as two rows.

Source File:
Col1, Col2,Col3
1, "abc",100
2,"ab
c",200
3,"ab

c",300

By using 'multiline rows' option, I was able to read row 1 and 2. However row3 is read as 2 records(refer below output). Any suggestion please?

Col1, Col2,Col3
1, "abc",100
2,"abc",200
3,"ab",
"c",300,


azure-data-factory
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ShaikMaheer-MSFT avatar image
0 Votes"
ShaikMaheer-MSFT answered

Hi @SudhakarMani-2921 ,

Thank you for posting query in Microsoft Q&A Platform.

I reproduced this case, by clearning schema inside dataset and then selecting Multiline Rows option helped to get data correctly under preview.

Please check below screenshot to get better idea.
202166-image.png

202192-image.png

Please Note, If you would like to refer columns from source transformation in further transformations, then please consider using byName() function in data flow expressions.

Between, you can also consider writing your own code in Azure Function to correct the data inside file and then consume that file in your dataflows.

Hope this helps. Please let us know if any further queries.


Please consider hitting Accept Answer button. Accepted answers help community as well.



image.png (184.4 KiB)
image.png (163.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SudhakarMani-2921 avatar image
0 Votes"
SudhakarMani-2921 answered

Thank you for the answer.
I also noticed another issue. The string column value has multiple double quotes with in the delimiter quotes (refer the example below). After transforming all "" to \"\", I was able to read the file with no issues.

Source File:
Col1, Col2,Col3
1, "abc",100
2,"ab
c",200
3,"ab "" xxxxx ""

c",300

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.