BCP Command Failed - Error

[ViVe-IT]

Hi,

I'm testing this on a CentOs with Python 3.6 and the latest pandas version available in my repo.
BCP Version => Version: 17.7.0001.1

Reading via creds.engine works, so the connection seems to be ok, but writing via to_sql just leads to the following Error:
raise BCPandasException(f"Bcp command failed with exit code {ret_code}")
bcpandas.constants.BCPandasException: Bcp command failed with exit code 1

Any ideas?

to_sql(df, 'TEST', creds, index=False, if_exists='replace')

[yehoshuadimarsky]

Do you have any logging output you could capture and share?

[ViVe-IT]

Hi, Unfortunately not really, this is the only Error Message the Library offers so far:
(ret_code seems to be unfilled)

raise BCPandasException(f"Bcp command failed with exit code {ret_code}")
bcpandas.constants.BCPandasException: Bcp command failed with exit code 1

[yehoshuadimarsky]

There should be more logging and output than just that. I specifically made all output get logged, this snippet is the function that actually invokes the BCP tool:

bcpandas/bcpandas/utils.py

Lines 210 to 225 in db37a2c

	def run_cmd(cmd: List[str]) -> int:
	"""
	Runs the given command.
	Prints STDOUT in real time, prints STDERR when command is complete,
	and logs both STDOUT and STDERR.
	Paramters
	---------
	cmd : list of str
	The command to run, to be submitted to `subprocess.Popen()`
	Returns
	-------
	The exit code of the command
	"""

[ViVe-IT]

Hi,

After testing around a little bit, the Traceback itself stays the same with:
raise BCPandasException(f"Bcp command failed with exit code {ret_code}") bcpandas.constants.BCPandasException: Bcp command failed with exit code 1

But before that there is actually an unknown argument issue, how should I pass the port info to SqlCreds?
For reading it works as 'Server, Port', but when writing it won't pase it correctly?

[odever]

Hi,

I got similar error. In my cased this was caused by the date format in temporary csv file created. It can be solved easily with a minor change by adding date_format='%Y-%m-%d %H:%M:%S' to to_csv() .

    df.replace({True: 1, False: 0}).to_csv(
        path_or_buf=csv_file_path,
        sep=delim,
        header=False,
        index=False,  # already set as new col earlier if index=True
        quoting=csv.QUOTE_MINIMAL,  # pandas default
        quotechar=quotechar,
        line_terminator=NEWLINE,
        doublequote=True,
        escapechar=None,  # not needed, as using doublequote
        date_format='%Y-%m-%d %H:%M:%S'
    )

[Mukesh944]

I am getting the following error linked to the same as above.
Can anyone help me on this.
Aprreciate your help
Thanks

• Inventory Report Supply\ATR - Inventory Report Supply>bcp dbo.CA_SAP_ZVISM_FullData_Pull in \AppData\Local\Temp\B9otHzggAVTtp4hwiBfD1 -S ABCSTLD6607 -d DevCell -q -T -b 204742 -f AppData\Local\Temp\fAmSfUSRlDPMvVAroQJxi
  -h "FIRE_TRIGGERS"
  2022-11-18 11:08:39,329 - bcpandas.utils - INFO - file: utils.py - function: run_cmd - line: 248 - - Inventory Report Supply\ATR - Inventory Report Supply>bcp dbo.CA_SAP_ZVISM_FullData_Pull in
  AppData\Local\Temp\B9otHzggAVTtp4hwiBfD1 -S ABCSTLD6607 -d DevCell -q -T -b 204742 -f AppData\Local\Temp\fAmSfUSRlDPMvVAroQJxi -h "FIRE_TRIGGERS"
  ["'bcp' is not recognized as an internal or external command,\n", 'operable program or batch file.\n']2022-11-18 11:08:39,334 - bcpandas.utils - ERROR - file: utils.py - function: run_cmd - line: 255 - ["'bcp' is not recognized as an internal or external command,\n", 'operable program or batch file.\n']
  2022-11-18 11:08:39,340 - sql_ops.odbc - ERROR - file: odbc.py - function: insert_dataframe - line: 202 - Bcp command failed with exit code 1
  Traceback (most recent call last):
• Inventory Report Supply/ATR - Inventory Report Supply/main_Inventory_Report_Supply.py", line 76, in
  main()
  ATR - Inventory Report Supply/ATR - Inventory Report Supply/main_Inventory_Report_Supply.py", line 38, in main
  conn.insert_dataframe(config.sql_table_name, DataCleansing.merged_df, schema = config.sql_schema )#, conn = conn)
  File "c:\Users\Mukesh.Bogineni\OneDrive - Anheuser-Busch InBev\My Documents\2022\ATR - Inventory Report Supply\ATR - Inventory Report Supply\sql_ops\odbc.py", line 187, in insert_dataframe
  bcp_path="bcp_with_triggers.bat")
  AppData\Local\Programs\Python\Python37\lib\site-packages\bcpandas\main.py", line 458, in to_sql
  bcp_path=bcp_path,
  \AppData\Local\Programs\Python\Python37\lib\site-packages\bcpandas\utils.py", line 116, in bcp
  raise BCPandasException(f"Bcp command failed with exit code {ret_code}")
  bcpandas.constants.BCPandasException: Bcp command failed with exit code 1

[gordthompson]

["'bcp' is not recognized as an internal or external command,\n", 'operable program or batch file.\n']

bcp.exe is not on your PATH. In my case I had to add

C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\

You also have the option to specify the full path by using the bcp_path= argument to to_sql().