bcp import error: Unexpected EOF encountered in BCP data-file

Question

Using bcp I'm trying to import a csv file to an Azure SQL Db table. But the following command gives the error shown below:

bcp US_SBA_COVI19_NYS in C:\Tmp\US_SBA_COVID19.csv -S myAzuresqlserver.database.windows.net -d MySQLDb -U azure-sa -P myPassword -q -c -t ","  

Unexpected EOF encountered in BCP data-file

Remarks:

1. There are plenty of online posts on this error but none of those solutions (using -F 2 switch, changing -c to -n, adding -r \n or \r etc.) worked for me. I downloaded the csv file from here on Data.gov. Maybe, the error has something to do with the file - and if someone can make it work for that file, I would like to hear about the solution.
2. I can successfully import the same csv to the same Db by using Import/Export wizard of SQL Server. But it needs to be done via bcp
3. I'm using the latest version of bcp and the Azure, and Windows-10 Pro.

Question: What could be a cause of the error and how can we fix it?

@Erland Sommarskog

Answer 1

You will not be able to load that file with BCP. You will need to upload it to BLOB storage, so that you can use the CSV option with BULK INSERT
Check out my article Using the Bulk-Load Tools in SQL Server and particularly the section on File format for details:

In your statement, you say that every comma is a field separator, but it isn't. Here is one sample line:

1029567410,2020,5,3,202,PPP,"BEDDING ACQUISITION, LLC",60 EAST 42ND ST SUITE 1250,NEW YORK,NY,10165-1299,2021,7,16,Paid in Full,24,1,10000000,10000000,0,,119918,East West Bank,"135 N Los Robles Ave, 7th Fl",PASADENA,CA,91101-4525,U,N,N,New Business or 2 years or less,NEW YORK,NEW YORK,NY,10165-1299,NY-12,500,314999,Unanswered,Unknown/NotStated,,10000000,,,,,,Limited  Liability Company(LLC),119918,East West Bank,PASADENA,CA,Unanswered,Unanswered,,10110556,2021,6,11

Do you think that the comma after ACQUISITION is a field terminator? BCP sure thinks so, because you told it.

Had the double quotes been applied consistently, there are some tricks you can play with format files. But in the file, fields are only quoted if they include a comma.

Answer 2

As Erland said,

BCP will not import files with optional double quotes around strings. SSIS will handle that correctly. The import/export wizard uses SSIS.