Insert CSV data into MSSQL using python, where col headers contain a hyphen

Question

Using Code found on this site we are able to load data from a CSV to MSSQL where col headers = firstname or first_name. However if the col header contains a hyphen such as first-name then it fails.

Portion of code:

with open(yourcsv) as csvfile:
       csvFile = csv.reader(csvfile, delimiter=',')
       header = next(csvFile)
       headers = map((lambda x: x.strip()), header)
       insert = 'INSERT INTO {} ('.format(table) + ', '.join(headers) + ') VALUES '
       for row in csvFile:
           values = map((lambda x: "'"+x.strip()+"'"), row)
           cursor.execute(insert +'('+ ', '.join(values) +');')
           conn.commit() #must commit unless your sql database auto-commits

Error = cursor.execute(insert +'('+ ', '.join(values) +');') pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '-'. (102) (SQLExecDirectW)")

We need to be able to consume files which can contain a hyphen in the col name.

score 1 · Accepted Answer · answered Mar 29 '19 at 17:54

This hard to read snippet of the code can be fixed by using square brackets to surround columns names:

insert = 'INSERT INTO {} ('.format(table) + '[' + '], ['.join(headers) + ']) VALUES '

More details: What is the use of the square brackets [] in sql statements?

The brackets are required if you use keywords or special chars in the column names or identifiers. You could name a column [First Name] (with a space)--but then you'd need to use brackets every time you referred to that column.

Insert CSV data into MSSQL using python, where col headers contain a hyphen

1 Answers1