Writing a csv file into SQL Server database using python

Posted on

Question :

Writing a csv file into SQL Server database using python

Hi I am trying to write a csv file into a table in SQL Server database using python. I am facing errors when I pass the parameters , but I don’t face any error when I do it manually. Here is the code I am executing.

cur=cnxn.cursor() # Get the cursor
csv_data = csv.reader(file(Samplefile.csv')) # Read the csv
for rows in csv_data: # Iterate through csv
    cur.execute("INSERT INTO MyTable(Col1,Col2,Col3,Col4) VALUES (?,?,?,?)",rows)
cnxn.commit()

Error :pyodbc.DataError: (‘22001’, ‘[22001] [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated. (8152) (SQLExecDirectW); [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. (3621)’)

However when I insert the values manually. It works fine

cur.execute("INSERT INTO MyTable(Col1,Col2,Col3,Col4) VALUES (?,?,?,?)",'A','B','C','D')

I have ensured that the TABLE is there in the database, data types are consistent with the data I am passing. Connection and cursor are also correct. The data type of rows is “list”

Asked By: NG_21

||

Answer #1:

Consider building the query dynamically to ensure the number of placeholders matches your table and CSV file format. Then it’s just a matter of ensuring your table and CSV file are correct, instead of checking that you typed enough ? placeholders in your code.

The following example assumes

  1. CSV file contains column names in the first line
  2. Connection is already built
  3. File name is test.csv
  4. Table name is MyTable
  5. Python 3
...
with open ('test.csv', 'r') as f:
    reader = csv.reader(f)
    columns = next(reader) 
    query = 'insert into MyTable({0}) values ({1})'
    query = query.format(','.join(columns), ','.join('?' * len(columns)))
    cursor = connection.cursor()
    for data in reader:
        cursor.execute(query, data)
    cursor.commit()

If column names are not included in the file:

...
with open ('test.csv', 'r') as f:
    reader = csv.reader(f)
    data = next(reader) 
    query = 'insert into MyTable values ({0})'
    query = query.format(','.join('?' * len(data)))
    cursor = connection.cursor()
    cursor.execute(query, data)
    for data in reader:
        cursor.execute(query, data)
    cursor.commit()
Answered By: Bryan

Answer #2:

You can pass the columns as arguments. For example:

for rows in csv_data: # Iterate through csv
    cur.execute("INSERT INTO MyTable(Col1,Col2,Col3,Col4) VALUES (?,?,?,?)", *rows)
Answered By: arocks

Answer #3:

I modified the code written above by Brian as follows since the one posted above wouldn’t work on the delimited files that I was trying to upload. The line row.pop() can also be ignored as it was necessary only for the set of files that I was trying to upload.

def upload_table(path, filename, delim, cursor):
    """
    Function to upload flat file to sqlserver
    """
    tbl = filename.split('.')[0]
    cnt = 0
    with open (path + filename, 'r') as f:
        reader = csv.reader(f, delimiter=delim)
        for row in reader:
            row.pop() # can be commented out
            row = ['NULL' if val == '' else val for val in row]
            row = [x.replace("'", "''") for x in row]
            out = "'" + "', '".join(str(item) for item in row) + "'"
            out = out.replace("'NULL'", 'NULL')
            query = "INSERT INTO " + tbl + " VALUES (" + out + ")"
            cursor.execute(query)
            cnt = cnt + 1
            if cnt % 10000 == 0:
                cursor.commit()
        cursor.commit()
    print("Uploaded " + str(cnt) + " rows into table " + tbl + ".")
Answered By: Indrajit

Answer #4:

I got it sorted out. The error was due to the size restriction restriction of table. It changed the column capacity like from col1 varchar(10) to col1 varchar(35) etc. Now it’s working fine.

Answered By: NG_21

Answer #5:

You can also import data into SQL by using either:

  • The SQL Server Import and Export Wizard
  • SQL Server Integration Services (SSIS)
  • The OPENROWSET function

More details can be found on this webpage:
https://docs.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql?view=sql-server-2017

Answered By: B. Latif

Leave a Reply

Your email address will not be published.