What to do When CSV acts Silly

We found an interesting issue in one of our tests. The plan was to dump a large CSV file at a location accessible to a database and then send the DB a BULK INSERT instruction, pointing to the source file.

The issue arose during the DB import process. MS SQL Server raised an Unexpected End of File Exception – even though the CSV (created by MuleSoft) was valid.

As it turns out, SQL Server did not like our line break. Mule used the default Carriage Return + Line Feed (“\r\n”), while SQL Server was expecting only the line feed (“\n”).

In the end, the Mule stepped up & the fix was easy. One additional header: in our Dataweave script.

%dw 2.0
output application/csv separator=",", header=false, lineSeparator="\n"
---
[
{
"line": "1"
},
{
"line": "2"
]

Adding the lineSeparator directive in the above script, and specifying the values as “\n” (line feed only, changed the output file to the expected format… and SQL Server was happy.