Converting csv to to xlsx but TextQualifier and EOL don't work

Question

I have gotten a csv file that consists of 7 columns and around 100 rows. A row would look likes this:

A-1,1,ABC,01/01/2024,"a,sentence","a, paragraph in one cell
with some, line breaks
with some, line breaks", another sentence

I tried converting the csv using EPPLUS

FileInfo csvFile = new FileInfo(filePath);
using (ExcelPackage excel = new ExcelPackage())
{
    var format = new ExcelTextFormat();
    worksheet.Cells[1, 1 , csvData.Count, colCount].LoadFromText(csvFile, format, OfficeOpenXml.Table.TableStyles.Custom, true);
}

I don't know if the paragraph cells that contain line breaks are messing with EPPLUS but for some reason it is having difficulty detecting the EOL. All lines are loaded into row 1. Not only that, the paragraph cell would be split into 4 cells due to it containing the delimiter

I have tried adding format.TextQualifier = '\"'; but I would get the this error Text delimiter is not closed in line: and the line in question is the entire csv file bc it is loading the whole csv file as 1 single line...Adding format.EOL = "\r\n"; or format.EOL = "\r"; doesn't do anything, all data will still be loaded into row 1 with the same incorrect splitting. Setting firstRowIsHeader as true doesn't do anything either as again it can't detect when a new row starts.

I can't alter the original csv file to change its delimiter or fix its line breaks. How can I fix the EOL and use TextQualifier? Am I missing something? Please!

Answer 1

format.EOL = "\r\n" and format.EOL = "\r"; didn't work but format.EOL = "\n"; does work. After some trials and errors I set this as the EOL and can finally set the TextQualifier without running into that "Text delimiter is not closed in line" error, and everything is splitting correctly now.