Import File into SQL Server using BCP

Question

My Files table has 3 columns:

ID int, FileName varchar(100), File varbinary(max)

I am trying to load a locally stored Excel file output.xlsx into this Files table using the BCP utility.

When I use the following command:

bcp TEST.dbo.files in C:\Users\Desktop\SampleTestFiles\OutputXULELogFiles\Test.xlsx -T -c

I get this error:

SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'TEST.dbo.files'.
SQLState = 37000, NativeError = 11529
Error = [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The metadata could not be determined because every code path results in an error; see previous errors for some of these.

BCP works on plain text files. If you export your Excel file to a .csv you'd be able to use BCP on it. Otherwise you would need to use something like SSIS, the SQL Import Wizard or OPENROWSET with the correct provider. — squillman
"bcp works on plan text files" while this is true in this case (bcp cannot load an excel file) it is not true that bcp ONLY works with text. BCP can also load files with native, proprietary SQL binary data. See the -N option. Text will still appear as text in the file, but non-text data types will be stored in the file as native SQL. It will appear a gobbledy-gook to a text viewer. Naturally, such files would have come FROM sql server to begin with. — jamie

Vignesh Kumar A Vignesh Kumar A · Accepted Answer · 2019-12-13T05:30:10

In fact, if you have more than an sql server instance within your server or box or machine and you dont specify the server name then the bcp connect to the default instance by default, then if your data table stored procedure or what ever it is, is stored within one of the named instances and Of Corse if you dont specify that server named instance full name, then the Native error = 208 will be raised and this is obviously logic because the data source that you are handling exists within the named instance and not on the default one, hence the command must be

bcp <databasename.schema.tablename> in|out  <complete target path where data will be imported|exported> -S<Full server named instance name> -T |or specify credentials through -P/-U    -c|-n

Import File into SQL Server using BCP

1 Answers