CODEWITHSUNDEEP
pythonsqlsql-serverbulkinsertbcp
Haris Muzaffar
Haris Muzaffar

Reputation: 441

How to specify length prefix in bcp command?

I'm using bcp tool to import CSV into a sql server table. I'm using python subprocess to execute the bcp command. My sample bcp command is like below:

bcp someDatabase.dbo.sometable IN myData.csv -n -t , -r \n -S mysqlserver.com -U myusername -P 'mypassword'

the command executes and says

0 rows copied.

Even if i remove the -t or -n option, the message is still the same. I read from sql server docs that there is something called length prefix(if bcp tool is used with -n (native) mode).

How can i specify that length prefix with bcp command?

My goal is to import CSV into a sql server table using bcp tool. I first create my table according to my date in the CSV file and i dont create a format file for bcp. I want all my data to be inserted correctly(according to the data type i have sepecified in my table).

Upvotes: 0

Views: 1159

Answers (2)

jamie
jamie

Reputation: 775

It is a very common practice with ETL tasks to first load text files into a "load" table that has all varchar/char data types. This avoids any possible implied data conversion errors that are more difficult/time-consuming to troubleshoot via BCP. Just pass the character data in the text file into character datatype columns in SQL Server. Then you can move data from the "load" table into your final destination table. This will allow you to use the MUCH more functional T-SQL commands to handle transformation of data types. Do not force BCP/SQL Server to transform your data-types for you by going from text file directly into your final table via BCP.

Also, I would also suggest visually inspecting your incoming data file to confirm it is formatted as specified. I often see mixups betweeen \n and \r\n for line terminator.

Last, when loading the data, you should also use the -e option as Neeraj has stated. This will capture "data" errors (it does not report command/syntax errors; just data/formatting errors). Since you incoming file is an ascii text file, you DO want to use the -c option for loading into the all-varchar "load" table.

Upvotes: 1

Neeraj Agarwal
Neeraj Agarwal

Reputation: 1059

If it is a csv file then do not use -n, -t or -r options. Use -e errorFileName to catch the error(s) you may be encountering. You can then take the appropriate steps.

Upvotes: 1

Related Questions