Problem trying to upload CSV to Database using bcp in Python

Question

I am having a problem trying to upload a CSV file to a Azure MS SQL database through bcp.

I am using the Bcpy tool to achieve this.

This is the script I am running:

sql_config = {
            'server': 'sql_server_hostname',
            'database': 'database_name',
            'username': 'user',
            'password': 'password'
        }
        sql_table_name = 'test_data1'
        csv_file_path = 'data1.csv'            #File in the script directory
        flat_file = bcpy.FlatFile(qualifier='', path=csv_file_path)
        sql_table = bcpy.SqlTable(sql_config, table=sql_table_name)
        flat_file.to_sql(sql_table)

After running the script, I receive the following error:

<ipython-input-11-97d18f6b2041> in function()
    263         flat_file = bcpy.FlatFile(qualifier='', path=csv_file_path)
    264         sql_table = bcpy.SqlTable(sql_config, table=sql_table_name)
--> 265         flat_file.to_sql(sql_table)
    266 
    267 

c:\users\user\appdata\local\programs\python\python37\lib\site-packages\bcpy\data_objects.py in to_sql(self, sql_table, use_existing_sql_table, batch_size)
    157                 ),
    158                 username=sql_table.username,
--> 159                 password=sql_table.password)
    160         bcp(sql_table=sql_table, flat_file=self, batch_size=batch_size)
    161 

c:\users\user\appdata\local\programs\python\python37\lib\site-packages\bcpy\binary_callers.py in sqlcmd(server, database, command, username, password)
     81                      ['-s,', '-W', '-Q', command]
     82     result = subprocess.run(sqlcmd_command, stdout=subprocess.PIPE,
---> 83                             stderr=subprocess.PIPE)
     84     if result.returncode:
     85         result_dump = str(result).replace(password, sha512(password))

c:\users\user\appdata\local\programs\python\python37\lib\subprocess.py in run(input, capture_output, timeout, check, *popenargs, **kwargs)
    470         kwargs['stderr'] = PIPE
    471 
--> 472     with Popen(*popenargs, **kwargs) as process:
    473         try:
    474             stdout, stderr = process.communicate(input, timeout=timeout)

c:\users\user\appdata\local\programs\python\python37\lib\subprocess.py in __init__(self, args, bufsize, executable, stdin, stdout, stderr, preexec_fn, close_fds, shell, cwd, env, universal_newlines, startupinfo, creationflags, restore_signals, start_new_session, pass_fds, encoding, errors, text)
    773                                 c2pread, c2pwrite,
    774                                 errread, errwrite,
--> 775                                 restore_signals, start_new_session)
    776         except:
    777             # Cleanup if the child failed starting.

c:\users\user\appdata\local\programs\python\python37\lib\subprocess.py in _execute_child(self, args, executable, preexec_fn, close_fds, pass_fds, cwd, env, startupinfo, creationflags, shell, p2cread, p2cwrite, c2pread, c2pwrite, errread, errwrite, unused_restore_signals, unused_start_new_session)
   1176                                          env,
   1177                                          os.fspath(cwd) if cwd is not None else None,
-> 1178                                          startupinfo)
   1179             finally:
   1180                 # Child is launched. Close the parent's copy of those pipe

FileNotFoundError: [WinError 2] The system cannot find the specified file

When filling the "sql_table_name" parameter I tried with both "test_data1" and "dbo.test_data1".

Since its an Azure MS SQL database, the server parameter was written like: "servername.database.windows.net"

Before using this tool, I also tried using bcp through os.system(). It didn't print any error, but didn't upload any row from the CSV to the database neither. This was the script:

 command = 'bcp "dbo.test_data1" in "data1.csv" -S"servername.database.windows.net" -d"database_name" -F2 -c -t"," - U"user" -P"password" -e error.txt'
os.system(command)

Do you know what may be causing this? Do you know about any other option to upload CSV files to my database?

Thank you!

Answer 1

The error is :

FileNotFoundError: [WinError 2] The system cannot find the specified file

Are you sure the following line

csv_file_path = 'data1.csv'            #File in the script directory

Is the complete path to the file ?