When migrating into Drupal from a CSV file (i.e. a spreadsheet from Excel or LibreOffice Calc if you're an open source nut like me), you use the MigrateSourceCSV class. It works very well.
Depending on where the CSV file originated, though, you might get unexpected behaviors, like Migrate UI telling you there are thousands of source fields you need to map. If this happens, and you don't have that many fields, check the line endings on your CSV file. Open the CSV file in your text editor and check its settings. Today, I was working with a file with the following settings (shown in BBEdit's status bar):
As you can see, the character set was Western (Mac OS Roman), and the line endings were Classic Mac. That means the file originated in Excel for the Mac, and it causes Migrate to freak out. I changed these settings to UTF-8 and Unix-style line breaks, saved the file, and from then on Migrate was happy.
There's also a parameter you'll see in the MigrateSourceCSV documentation called embedded_newlines. It's supposed to help with weird row counts, so I thought maybe that would help this issue, but apparently that's not quite it. It's not a big deal to resave the CSV file anyway, so that's what I'd recommend just to be safe.