BCP returns no errors, but also doesn't copy any rows
The bcp command typically needs an identifier to specify the format mode of the bcp file.
- -c specifies character (plaintext) mode
- -n specifies native mode
- -w specifies unicode mode
In your test case, the file you created is plaintext, so you should specify '-c' in your bcp in command.
bcp MyDB.dbo.mincase in data.csv -c -T -S .\SQLEXPRESS
Microsoft Recommends using the '-n' for imports and exports to avoid issues with field delimiters appearing within column values (See section on Character Mode and Native Mode Best Practices).
I ran into a similar issue, except that I was already using a format file, so adding one of the format flags wouldn't help. For anyone else who ends up here, I want to explain what caused this issue for me (and hopefully help explain what the underlying issue cause is).
The issue is that BCP doesn't actually process lines of text in a file; rather, it just processes a stream of data, according to the instructions you give it. This means that newlines will be ignored, if that's what you (accidentally) tell BCP to do.
In my case, this turned out to be a typo in the last line in the format file:
13.013481 SQLCHAR 0 21 "," 1 RecordKey ""2 SQLCHAR 0 30 "," 0 SubmissionKey ""3 SQLCHAR 0 1 "," 2 A1cLvl ""...1347 SQLCHAR 0 1 "," 0 WoundIntVac ""1348 SQLCHAR 0 1 "/r/n" 0 XClampTm ""
If you look closely, you'll see the slashes are backwards in the terminator field on the last line. So, instead of looking for Windows-style line endings, BCP was actually looking for the text string "/r/n" in the data stream.
Since the string didn't actually appear in my data at all, BCP never actually found anything matching my final field. So, it makes sense that it found "0 rows" to copy.
I'm still not sure why this doesn't result in something like an "Unexpected EOF encountered" error or something, but hopefully someone else will be able to expand on this.