CODEWITHSUNDEEP
pythonsql-server-2008csvimport
LauraJ
LauraJ

Reputation: 63

Python script write multiple CSV files into SQL Server table error

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!"

Upvotes: 0

Views: 4703

Answers (1)

Markku K.
Markku K.

Reputation: 3908

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".

Upvotes: 3

Related Questions