A colleague of mine that works in finance downloaded a Comma Separated Values (CSV) file from a bank, and opened it in Excel as she always does.

Said file was basically all the transactions for a particular month.

What happened is it only showed about a third of the data that was expected in the Excel file. It would get to a particular cell and that would be the end of the data in Excel, although she could see on the webpage that there was lots more transactions.

All other months were fine just this one file for that one particar month.

So she calls our internal help desk. They take a look and say it must be something wrong with the bank or the file, and suggested they contact the bank.

She contacts her bank via phone, they say they don't know what to do, but to contact the (business banking) team via email.

She contacts the (business banking) team via email, they say they can't assist with this, but to contacts the banks internal IT Helpdesk

She contacts the banks internal IT Helpdesk who say they only assist bank employees and not the public, and suggested she calls our own internal Helpdesk.

Eventually she messages me on Teams and says although she knows this isn't what I look at, she has spent 2 hours contacting various teams and no one will assist.

I head over to her desk to take a look and notice where the data discrepancy between the website and csv file is, there is a single quotation mark, ie a " as one of the fields has a single quotation mark in it.

With a csv file, if you want to have something literal in there, like a comma, you need to put a quotation mark at the beginning and end of something, such as ","

Opening the file in notepad, I could see that all the data after the quotation mark was not showing in Excel, and in the entire CSV file there was only the single quotation mark. All the data was in the CSV file.

So removed the quotation mark and saved the file. I then opened it in Excel and all the data was showing as expected.

So it seems with Excel, if you have a CSV with a single quotation mark and no 2nd quotation mark to delimit it, it will just ignore all data after the quotation mark.

I explain this to my colleagues, who is just ecstatic that this is working now, and understands what is going on.

The next morning when I come into the office, on my desk is a big basket of freshly baked cookies and a handwritten note.

The note explains how without me working out what was wrong, she would have had to spend several hours copying and pasting, field by field, for hundreds of lines from the website to Excel.