I am writing simple Python code to extract data from a CSV file and send it to a SQL Server database. But I get this field 07002 COUNT incorrect or syntactic. A CSV file has several meanings, and if I send only one row of data, it inserts a penalty. The problem is with multiple values in the CSV file. Any help is appreciated!
Here is my code:
import_data = [] for csvFile in glob.glob("*.csv"): with open(csvFile, 'r') as f: reader = csv.reader(f)<br> next(reader, None)#This skips the headers. import_data = [tuple(line) for line in csv.reader(f)]
Insert data from CSV file into SQL Server
cur.executemany("""INSERT INTO dbo.currentobservations(STID,NAME,ST,LAT,LON,YR,MO,DA,HR,MI,TAIR,TDEW,RELH,CHIL,HEAT,WDIR,WSPD,WMAX,PRES,TMAX,TMIN,RAIN) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""", import_data) #Here is the error message I am getting: Traceback (most recent call last): File "E:\Python3\Programs\sqlservermesonetcsv.py", line 40, in <module> cur.executemany("""INSERT INTO dbo.currentobservations(STID,NAME,ST,LAT,LON,YR,MO,DA,HR,MI,TAIR,TDEW,RELH,CHIL,HEAT,WDIR,WSPD,WMAX,PRES,TMAX,TMIN,RAIN) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""", import_data) File "E:\Python3\lib\site-packages\pypyodbc.py", line 1671, in executemany self.execute(query_string, params, many_mode = True) File "E:\Python3\lib\site-packages\pypyodbc.py", line 1605, in execute self.execdirect(query_string) File "E:\Python3\lib\site-packages\pypyodbc.py", line 1631, in execdirect check_success(self, ret) File "E:\Python3\lib\site-packages\pypyodbc.py", line 986, in check_success ctrl_err(SQL_HANDLE_STMT, ODBC_obj.stmt_h, ret, ODBC_obj.ansi) File "E:\Python3\lib\site-packages\pypyodbc.py", line 966, in ctrl_err raise DatabaseError(state,err_text) pypyodbc.DatabaseError: ('07002', '[07002] [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error')