I want to import data from my Excel file to several tables in my access database. I already mapped all data I wanted to import from my xlsx.file to the column names in the access data tables, but somehow fail to get the insert statement right. Could you help me?
#import necessary modules
import pandas as pd
import pyodbc
# ODBC connection
connection_string = r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\User\Downloads\OneDrive_2023-07-24\Fragebogen Rohdaten\ArtiPro.accdb;'
conn = pyodbc.connect(connection_string)
def import_excel_to_access(excel_file_path, table_mappings):
# Read the Excel file into a pandas DataFrame
df = pd.read_excel(r'C:\Users\User\Downloads\OneDrive_2023-07-24\Fragebogen Rohdaten\A001.xlsx')
# Create a cursor to execute SQL queries
cursor = conn.cursor()
for table_name, column_mapping in table_mappings.items():
# Prepare the INSERT query with selected columns
insert_query = f"INSERT INTO table_name ({', '.join(column_mapping.values())}) VALUES ({', '.join(['?' for _ in column_mapping.values()])})"
print(insert_query)
# Insert data into the Access table
for row in df.rename(columns=column_mapping)[column_mapping.values()].itertuples(index=False):
cursor.execute(insert_query, row)
print(insert_query)
# Commit the changes and close the connection
conn.commit()
conn.close()
if __name__ == '__main__':
excel_file_path = r'C:\Users\User\Downloads\OneDrive_2023-07-24\Fragebogen Rohdaten\A001.xlsx'
table_mappings = {
'01_Basic Data': {
'SID': 'SID',
'1': 'AD intake',
'2a': 'AD trade name',
'2b': 'AD substance',
'3a': 'Drug Dose (in mg)',
'3b': 'Drug Rhythm',
'4': 'Age',
'5': 'Gender',
'6': 'Ethnicity'
},
'02_Medication Effect': {
'SID': 'SID',
'7': 'Q7',
'8': 'Q8',
'8a': 'Q8a',
'9': 'Q9',
'9a': 'Q9a',
'10': 'Q10',
'10a': 'Q10a',
'11': 'Q11',
'11a': 'Q11a',
'12': 'Q12',
'12a': 'Q12a',
'13': 'Q13',
'13a': 'Q13a',
'14': 'Q14',
'14a': 'Q14a',
'15': 'Q15',
'16': 'Q16',
},
'03_ADRs': {
'SID': 'SID',
'NW_frei': 'ADRs (free speech)',
'17': 'Q17_dry mouth',
'17a': 'Q17a_dry mouth',
'17b': 'Q17b_dry mouth',
'17c': 'Q17c_dry mouth',
'17d': 'Q17d_dry mouth',
'17e': 'Q17e_dry mouth',
'18': 'Q18_blurred vision',
'18a': 'Q18a_blurred vision',
'18b': 'Q18b_blurred vision',
'18c': 'Q18c_blurred vision',
'18d': 'Q18d_blurred vision',
'18e': 'Q18e_blurred vision',
'19': 'Q19_dizziness',
'19a': 'Q19a_dizziness',
'19b': 'Q19b_dizziness',
'19c': 'Q19c_dizziness',
'19d': 'Q19d_dizziness',
'19e': 'Q19e_dizziness',
'20': 'Q20_fall',
'20a': 'Q20a_fall',
'20b': 'Q20b_fall',
'20c': 'Q20c_fall',
'20d': 'Q20d_fall',
'20e': 'Q20e_fall',
'21': 'Q21_increased appetite',
'21a': 'Q21a_increased appetite',
'21b': 'Q21b_increased appetite',
'21c': 'Q21c_increased appetite',
'21d': 'Q21d_increased appetite',
'21e': 'Q21e_increased appetite',
'22': 'Q22_reduced appetite',
'22a': 'Q22a_reduced appetite',
'22b': 'Q22b_reduced appetite',
'22c': 'Q22c_reduced appetite',
'22d': 'Q22d_reduced appetite',
'22e': 'Q22e_reduced appetite',
'23': 'Q23_weight gain',
'23a': 'Q23a_weight gain',
'23b': 'Q23b_weight gain',
'23c': 'Q23c_weight gain',
'23d': 'Q23d_weight gain',
'23e': 'Q23e_weight gain',
'24': 'Q24_weight loss',
'24a': 'Q24a_weight loss',
'24b': 'Q24b_weight loss',
'24c': 'Q24c_weight loss',
'24d': 'Q24d_weight loss',
'24e': 'Q24e_weight loss',
'25': 'Q25_nausea',
'25a': 'Q25a_nausea',
'25b': 'Q25b_nausea',
'25c': 'Q25c_nausea',
'25d': 'Q25d_nausea',
'25e': 'Q25e_nausea',
'26': 'Q26_vomiting',
'26a': 'Q26a_vomiting',
'26b': 'Q26b_vomiting',
'26c': 'Q26c_vomiting',
'26d': 'Q26d_vomiting',
'26e': 'Q26e_vomiting',
'27': 'Q27_bleeding',
'27a': 'Q27a_bleeding',
'27aa1': 'Q27aa1_bleeding',
'27aa2': 'Q27aa2_bleeding',
'27aa3': 'Q27aa3_bleeding',
'27aa4': 'Q27aa4_bleeding',
'27aa5': 'Q27aa5_bleeding',
'27aa6': 'Q27aa6_bleeding',
'27b': 'Q27b_bleeding',
'27c': 'Q27c_bleeding',
'27d': 'Q27d_bleeding',
'27e': 'Q27e_bleeding',
'28': 'Q28_constipation',
'28a': 'Q28a_constipation',
'28b': 'Q28b_constipation',
'28c': 'Q28c_constipation',
'28d': 'Q28d_constipation',
'28e': 'Q28e_constipation',
'29': 'Q29_diarrhoea',
'29a': 'Q29a_diarrhoea',
'29b': 'Q29b_diarrhoea',
'29c': 'Q29c_diarrhoea',
'29d': 'Q29d_diarrhoea',
'29e': 'Q29e_diarrhoea',
'30': 'Q30_urinary incontinence',
'30a': 'Q30a_urinary incontinence',
'30b': 'Q30b_urinary incontinence',
'30c': 'Q30c_urinary incontinence',
'30d': 'Q30d_urinary incontinence',
'30e': 'Q30e_urinary incontinence',
'31': 'Q31_urinary retention',
'31a': 'Q31a_urinary retention',
'31b': 'Q31b_urinary retention',
'31c': 'Q31c_urinary retention',
'31d': 'Q31d_urinary retention',
'31e': 'Q31e_urinary retention',
'33': 'Q33',
'weitere NW_frei': 'Notes'
}
}
import_excel_to_access(excel_file_path, table_mappings)
But somehow it fails me every time, tried a lot already
INSERT INTO table_name (SID, AD intake, AD trade name, AD substance, Drug Dose (in mg), Drug Rhythm, Age, Gender, Ethnicity) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
---------------------------------------------------------------------------
ProgrammingError Traceback (most recent call last)
<ipython-input-24-68b37adef976> in <module>
174
175
--> 176 import_excel_to_access(excel_file_path, table_mappings)
<ipython-input-24-68b37adef976> in import_excel_to_access(excel_file_path, table_mappings)
22 # Insert data into the Access table
23 for row in df.rename(columns=column_mapping)[column_mapping.values()].itertuples(index=False):
---> 24 cursor.execute(insert_query, row)
25
26 print(insert_query)
ProgrammingError: ('42000', '[42000] [Microsoft][ODBC-Treiber für Microsoft Access] Syntaxfehler in der INSERT INTO-Anweisung. (-3502) (SQLExecDirectW)')
