CODEWITHSUNDEEP
pythonsql-serverdatabasecsvpyodbc
Jamesdabeard
Jamesdabeard

Reputation: 31

"Conversion failed when converting date" while inserting CSV data

I am attempting to insert data into a SQL table from a CSV using the following code:

with requests.get(reportURL) as csvfile:
    decoded_content=csvfile.content.decode('utf-8')
    reader = csv.reader(decoded_content.splitlines(), delimiter=',')
    columns = next(reader,None)
    query = "INSERT INTO SQL_TABLE({0}) VALUES ({1})"
    query = query.format('[{0}]'.format('],['.join(columns)),','.join('?'*len(columns)))
    cursor = cnxn.cursor()
    cursor.execute(query, columns)
    for data in reader:
        cursor.execute(query, data)
    cursor.commit()

However, I am receiving the following error code:

cursor.execute(query, columns) pyodbc.DataError: ('22007', '[22007] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Conversion failed when converting date and/or time from character string. (241) (SQLExecDirectW)')

I am wondering if the issue is that the data is coming through as a string instead of date? Date is the first column, so I am not sure if any of the other columns would run into a similar issue. Columns are as follows:

Date | Advertiser | Campaign | Ad Group | Impressions

Data Type:

datetime | varchar | varchar | varchar | bigint

Thank you for the help!

Upvotes: 1

Views: 3316

Answers (2)

nir
nir

Reputation: 117

i dont know if its still relevant, but i had this error when trying to filter database by date from specific timestamp. i found that the problem was the data format of the reference point, something like this:'2020-07-26 12:07:22.263000'

the solution was taking the str(my date)[:19] to remove the access data after the seconds

Upvotes: 0

Gord Thompson
Gord Thompson

Reputation: 123829

cursor.execute(query, columns)

You are trying to execute the INSERT statement using the column names as data. SQL Server is complaining because you are trying to insert the value 'Date' into a datetime column. Removing that statement should make the problem go away.

Upvotes: 1

Related Questions