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.

Alexander Volok · Accepted Answer · 2019-03-29 17:54:26Z

1

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.

answered Mar 29, 2019 at 17:54

Alexander Volok

5,9903 gold badges19 silver badges34 bronze badges

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

1 Comment

pthfndr2007 Over a year ago

- hat tip to you!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related