title: pyodbc.DataError: ('22018',"[22018] [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed ] error
body: <p>I have written the following snippet to import a CSV file into an MS SQL Server database but it gives me an error. It is based on code written for <a href="https://stackoverflow.com/questions/2887878/importing-a-csv-file-into-a-sqlite3-database-table-using-python/2888042#2888042">Sqlite for Python</a> and changed for MSSQL.</p>
  
  <pre><code>import csv, pyodbc
  import logging
  
  def _get_col_datatypes(fin):
      dr = csv.DictReader(fin) # comma is default delimiter
      fieldTypes = {}
      for entry in dr:
          feildslLeft = [f for f in dr.fieldnames if f not in fieldTypes.keys()]
          if not feildslLeft: break # We're done
          for field in feildslLeft:
              data = entry[field]
  
              # Need data to decide
              if len(data) == 0:
                  continue
  
              if data.isdigit():
                  fieldTypes[field] = "INTEGER"
              else:
                  fieldTypes[field] = "TEXT"
          # TODO: Currently there's no support for DATE in sqllite
  
      if len(feildslLeft) &gt; 0:
          raise Exception("Failed to find all the columns data types - Maybe some are empty?")
  
      return fieldTypes
  
  
  def escapingGenerator(f):
      for line in f:
          yield line.encode("ascii", "xmlcharrefreplace").decode("ascii")
  
  
  def csvToDb(csvFile, outputToFile = False):
      # TODO: implement output to file
  
      with open(csvFile,mode='r') as fin:
          dt = _get_col_datatypes(fin)
  
          fin.seek(0)
  
          reader = csv.DictReader(fin)
  
          # Keep the order of the columns name just as in the CSV
          fields = reader.fieldnames
          cols = []
  
  
          # Set field and type
          for f in fields:
              cols.append("%s %s" % (f, dt[f]))
  
          # Generate create table statement:
          stmt = "CREATE TABLE ads (%s)" % ",".join(cols)
  
          con = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=sd;UID=Test;PWD=11')
          cur = con.cursor()
          cur.execute(stmt)
  
          fin.seek(0)
  
  
          reader = csv.reader(escapingGenerator(fin))
  
          # Generate insert statement:
          stmt = "INSERT INTO ads VALUES(%s);" % ','.join('?' * len(cols))
  
          cur.executemany(stmt, reader)
          con.commit()
  
      return con
  
  
  csvToDb('Books.csv')
  </code></pre>
  
  <p>The error I am getting is</p>
  
  <blockquote>
    <p>pyodbc.DataError: ('22018', "[22018] [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting the varchar value 'a' to data type int. (245) (SQLExecDirectW)")</p>
  </blockquote>
  
  <p>Also please suggest if you think there are any other methods to dynamically import CSV or text files into an MSSQL database</p>