Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to open BCP host data-file - Import by BCP

Question

I am trying to import data from Excel into SQL.

I try to use the BCP command, this the code:

EXEC master..xp_cmdshell 'bcp DB.dbo.Table IN "C:\\File.csv" -c -T -S ServerName\instancename'

but I get this error:

output
SQLState = 08001, NativeError = -1
Error = [Microsoft][ODBC Driver 13 for SQL Server]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. 
SQLState = 08001, NativeError = -1
Error = [Microsoft][ODBC Driver 13 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is config
ured to allow remote connections. For more information see SQL Server Books Online.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Login timeout expired
NULL

Also if I remove the serer property' it not sucsess' this the code:

EXEC master..xp_cmdshell 'bcp DB.dbo.Table IN "C:\\File.csv" -c -T'

the error:

output
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to open BCP host data-file
NULL

I can't know what to do... I'm look for many solutions but - NOTHING

Answer 1

After many google searches and my many attempts I found the problem:

1. I write the code by Bulk insert and not with BCP command, this is the full Code:

---

declare @SQL nvarchar(max) = '

CREATE TABLE #TempTable
( [Field1] nvarchar(max) NULL,
    [Field2] nvarchar(max) NULL,
    [Field3] nvarchar(max) NULL ); 


BULK INSERT #TempTable  FROM ''<FullPath>\FileName.csv'' WITH --if the path is in the network - need to write the Full-path of the drive
(
KEEPIDENTITY,
FIELDTERMINATOR = '','',
MAXERRORS = 10000,
KEEPNULLS, 
ROWTERMINATOR=''\n'',
FIRSTROW = 2,
CODEPAGE = ''1255''
);

select * from #TempTable
Insert into TableNameInDB(Field1,Field2,Field3)
select * from #TempTable
'

EXEC sp_executesql @SQL

1. The path of the file was with the full path of the drive (if the drive map in the network)
2. I save the file by csv file