When downloading a .CSV file the CRLF line breaks are missing, but Excel can load it fine. Can I fix the CRLF without loading Excel?

Question

I am retrieving daily logs from a system in the format of a .csv file. I want to import these to my database. The issue is that, for some reason, these .csv files seem to lack CRLF line endings (it's being replaced by LF). If I open the file in Notepad++, all the line endings are LF - this won't work because when uploading I split the file into the database by using CRLF to determine rows and LF to determine columns.

If I open the .csv file in Excel, and save it as a .csv file, the CRLF gets added and I can upload it fine to the database.

I can't understand how when I read the while with notepad++, all the endings are LF, however Excel can somehow differentiate which ones are really CRLF and which ones are LF?

The file is 4-8MB so it takes a while for Excel to open it (+-30sec?), so I was wondering if anyone knows what the problem is and if there's any way other way to solve it rather than opening it in Excel or a CSV editor and save it again. It's very unnecessary work for an otherwise automated process that I'd love to get rid of.

Thanks!

Correct format, after opening and saving with Excel

The csv file after downloading, without opening and saving again

Answer 1

It's a bit hard to tell, but if the question is: "why can excel open these", the answer is likely simple: Because they designed it to.

There's lots of incorrect text/csv generated across all software, so perhaps the Excel engineers simply decided that it was worth parsing these, even if it's wrong. It's not hard to write software that can recognize both \n and \r\n, that said... if you are generating CSV, you should use \r\n for the software that does care about line endings.