0

I am trying to insert a CSV file into SQL Server Express using Python.

I referenced Importing a csv file into sql server using python but I still can't get it to work. I have 24 column names in the CSV file, the database table is already created in SQL. The table name is [dev].[dbo].[Cust].

This is the error I am getting now

Traceback (most recent call last):
File "C:\Test\Cust.py", line 5, in
my_cursor = cnxn.cursor()
NameError: name 'cnxn' is not defined

Column names are

Schedule Date, Schedule In, Schedule Out, Schedule Area, Cust/Non Cust, 
account, Account ID, Last Name, Company Abbr, Company Name, Account Code, 
ADT, Supervisor, position, Available Open Time, Events, 8 - 11 am, 11 - 2 pm, 
2 - 5 pm, 5 - 8 pm, 8 - 11 pm, All Day, Total available, Comment

Any help would be greatly appreciated

import pyodbc
import csv

# DESTINATION CONNECTION
my_cursor = cnxn.cursor()
my_cnxn = pyodbc.connect('Driver=SQL Server;'
                      'Server=#######\SQLEXPRESS;'
                      'Database=DEV;'
                      'Trusted_Connection=yes;')
                      
print("Connected")

def insert_records(table, yourcsv, cursor, cnxn):
    #INSERT SOURCE RECORDS TO DESTINATION
    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 ({})' \
        .format(', '.join(len(headers) * '?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?'))  #                                   Add parameter placeholders as ?

        for row in csvFile:
            values = map((lambda x: x.strip()), row)  # No need for the quotes
            cursor.execute(insert, values)  # Pass the list of values as 2nd argument
            conn.commit()

table = 'Cust'
mycsv = 'C:\test\Cust.csv' # SET YOUR FILEPATH
insert_records(table, mycsv, my_cursor, my_cnxn)
cursor.close()
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rey745
  • 11
  • first use `my_cnxn = pyodbc.connect(...)` and next `my_cursor = my_cnxn.cursor()`. First you have to connect to database to get cursor. And don't forget to use `my_cnxn` instead of `cnxn` – furas Aug 28 '20 at 17:42
  • Thank you for the quick response! I made the changes and now i get a new error any incite? File "C:\Test\Cust.py", line 33, in insert_records(table, mycsv, my_cursor, my_cnxn) File "C:\test\Cust.py", line 23, in insert_records .format(', '.join(len(headers) * '?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?')) #Add parameter placeholders as ? TypeError: object of type 'map' has no len() – Rey745 Aug 28 '20 at 18:43
  • if you have new problem then you should create new question on new page - and you will have new place for code and description. And it will be more readabel. And maybe new persons will see it. – furas Aug 28 '20 at 20:30
  • if you have error then first you could use `print(...)` and `print( type(...) )` to see what you have in variables. Error shows problem with `len(headers)` then first you should check what you have in `headers`. In Python 2 `map` gives `list` but in Python 3 it gives object `map` and sometimes you have to use `list()` to convert it to list. - ie, `len( list(headers) )` or `headers = list(map(...))` – furas Aug 28 '20 at 20:33
  • BTW: object `str` has method `str.strip()` and instead of `map((lambda x: x.strip()), header)` you can do `map(str.strip, header)` – furas Aug 28 '20 at 20:37

0 Answers0