Error with delimiters on dataframe when trying to upload it to MSSQL

Question

So I've been trying to upload a dataframe to an specific table that is under MSSQL, I've trying to use the BCPANDAS library to upload the data to it. However there's an issue with the data that has a lot of strings on it that contains multiple characters.

The code that I'm using is the following:

from bcpandas import SqlCreds, to_sql
creds = SqlCreds(
  'server',
  'dbo',
  'username',
  'password'
  )

to_sql(df,'targeted_table',creds,index = False, if_exists='append', schema='test')

However anytime that I try to upload the data it yields this error

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Users\jdc33\AppData\Local\Programs\Python\Python39\lib\site-packages\bcpandas\main.py", line 394, in to_sql
    delim = get_delimiter(df) if delimiter is None else delimiter
  File "C:\Users\jdc33\AppData\Local\Programs\Python\Python39\lib\site-packages\bcpandas\constants.py", line 68, in get_delimiter
    raise BCPandasValueError(error_msg.format(typ="delimiter", opts=_DELIMITER_OPTIONS))
bcpandas.constants.BCPandasValueError: Data contains all of the possible delimiter characters (',', '|', '\t'),
cannot use BCP to import it. Replace one of the possible delimiter characters in
your data, or use another method besides bcpandas.

Further background:

https://docs.microsoft.com/en-us/sql/relational-databases/import-export/specify-field-and-row-terminators-sql-server#characters-supported-as-terminators

What I'm thinking is happening is that the data in the rows has a lot of strings that contains multiple of the delimiters (',', '|', '\t') that are mentioned in the error above hence creating an issue with how the data is uploaded. I've tried to set the delimiter to only one of the above mentioned by ingesting the file like this:

testdf= pd.read_csv('data.csv',delimiter=',')

But the error keeps showing up.

Has any one encounter this error and know how to fix it? Any assistance will be really helpful

Answer 1

So I manage to fix the issue with a simple error fix, as per question asked above the issue was that the delimiters were existing on the data in some columns, as per deep dive on the data and not to mess the integrity of it, I went over and replace all the instances of the string "," in order for the data to be ingested with BCPandas.