Unable to load txt file from python to sql server

Question

I keep getting this error when I try to load a txt file from python to SQL server. All other format files load pretty easily.

I am using sqlalchemy to create a engine. The other formats that I use are excel and csv. I tried converting the txt files to excel and load. still does not work.

import pandas as pd

from sqlalchemy import create_engine, VARCHAR, FLOAT,INTEGER, DATETIME, NVARCHAR

import urllib

quoted = urllib.parse.quote_plus("DRIVER={SQL Server};SERVER=SERVER;DATABASE=DB__DATABASE")

engine = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted), pool_pre_ping=True)

df.to_sql('tablename', con =engine, if_exists= 'append', index = False)

(pyodbc.Error) ('01000', '[01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv()). (10054) (SQLExecDirectW)')

Answer 1

The error message you provided suggests a network connectivity issue between your Python environment and the SQL Server. This error message indicates that the connection is lost or terminated unexpectedly during data transmission.

Here are a few things that you can try to resolve this error:

Check your network connection: Ensure that you have a stable network connection and that there are no interruptions or disruptions in your network. Try to ping your SQL Server to verify connectivity.

Check SQL Server configuration: Check your SQL Server configuration and ensure that it's configured to accept remote connections. You can check this by verifying that the TCP/IP protocol is enabled, and the appropriate ports are open.

Increase the packet size: Try increasing the packet size by adding the "packet size" parameter to the connection string. The default packet size is 4096, but you can increase this value to improve data transmission.

Update your SQL Server drivers: Ensure that you have the latest SQL Server drivers installed. You can download the drivers from the Microsoft website.

Try using a different method to load the data: If you're still experiencing issues, try using a different method to load the data. For example, you can try using the BULK INSERT statement or the SQL Server Import and Export Wizard.

Answer 2

Hi @Sai Manoj

As Vahid says, the error you're getting indicates a network connectivity issue.

In addition to the methods mentioned above, you can also refer to the official documentation for troubleshooting.

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/resolve-connectivity-errors-overview

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

The ODBC driver you are using is not the best one. It is the one that ships with the operating system, but it is twenty years old and was written when SQL 2000 ruled the world. You should download and install ODBC 18 Driver for SQL Server and change the connection string accordingly. No guarantee that this will resolve the issue, but it is certainly a start.