Python script write multiple CSV files into SQL Server table error

Question

i'm trying to write an entire folder of CSV files into a SQL Server Table. I'm getting the following error, and i'm really stumped:

Traceback (most recent call last):
  File "C:\\Projects\Import_CSV.py", line 37, in <module>
    cursor.execute("INSERT INTO HED_EMPLOYEE_DATA(Company, Contact, Email, Name, Address, City, CentralCities, EnterpriseZones, NEZ, CDBG)" "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", row)
DataError: ('22001', '[22001] [Microsoft][SQL Server Native Client 10.0][SQL Server]String or binary data would be truncated. (8152) (SQLExecDirectW); [01000] [Microsoft][SQL Server Native Client 10.0][SQL Server]The statement has been terminated. (3621)')

I'm not sure what's wrong in my code. I also need it to skip the first row in the CSV files as that is the header row. Any help would be greatly appreciated. Thank you.

# Import arcpy module
import csv
import arcpy
import pyodbc as p
import os

# Database Connection Info
server = "myServer"
database = "myDB"
connStr = ('DRIVER={SQL Server Native Client 10.0};SERVER=' + server + ';DATABASE=' + database + ';' + 'Trusted_Connection=yes')

# Open connection to SQL Server Table
conn = p.connect(connStr)

# Get cursor
cursor = conn.cursor()

# Assign path to Excel files
folder_to_import = "\\\\Server\\HED_DATA_CSV"
l_files_to_import = os.listdir(folder_to_import)
for file_to_import in l_files_to_import:
     if file_to_import.endswith('.CSV'):
         csv_files = os.path.join(folder_to_import, file_to_import)
         csv_data = csv.reader(file(csv_files))
         for row in csv_data:
             cursor.execute("INSERT INTO HED_EMPLOYEE_DATA(Company, Contact, Email, Name, Address, City, CentralCities, EnterpriseZones, NEZ, CDBG)" "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", row)

cursor.close()

conn.commit()

conn.close()

print"Script has successfully run!"

Markku K. · Accepted Answer · 2013-06-05 15:17:53Z

3

You can skip the first line this way:

csv_data.next() #throw away first row
for row in csv_data:
    if len(row) >= 10:
        cursor.execute("INSERT ..." ...)

Also, you should check to make sure that row contains enough elements before executing:

if len(row) >= 10: #use first ten values in row, if there are at least ten
    cursor.execute("INSERT ...", row[:10])

You currently have your insert statement listed as two strings next to each other. This has the effect of joining them together with no space in between. You may want a space before "VALUES".

edited Jun 5, 2013 at 15:17

answered Jun 4, 2013 at 22:23

Markku K.

3,93622 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LauraJ Over a year ago

Thank you for the reply. Now i'm getting the error "Traceback (most recent call last): File "C:/folder/Script_CSV.py", line 36, in <module> for row in csv_data[1:]: TypeError: '_csv.reader' object is unsubscriptable"

Collectives™ on Stack Overflow

Python script write multiple CSV files into SQL Server table error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related