CODEWITHSUNDEEP
pythonxlsx
Benjamin
Benjamin

Reputation: 37

How to create a python script to parse a xlsx spreadsheet file and generate sql statements?

I have a xlsx file with two columns (id, meal) and 100 rows of data, and I want to parse the data to generate a notepad file that has sql update statements.

Id Meal
12345 Child
23456 Adult
34567 Senior
34599 Senior

I'm unsure on how to implement if/else/else if statements and add data from the xlsx file to generate sql statements to add into a notepad file.

If the meal is 'Child', an example of the sql script generated would be update system.user set meal_name = 'Child', meal_price = 'Child' where customer_id = '12345';

If the meal is 'Adult', an example of the sql script generated would be update system.user set meal_name = 'Adult', meal_price = 'Adult' where customer_id = '23456';

If the meal is 'Senior', an example of the sql script generated would be update system.user set meal_name = 'Senior', where customer_id = '34567';

Anything to help would be very much appreciated, it's my first experience with Python so I'm unsure on how to get started.

This is the current code I have which doesn't have much, I'm just not sure how to get started

import openpyxl
from pathlib import Path

xlsx_file = Path('CustomerData', 'customer_data.xlsx')
wb_obj = openpyxl.load_workbook(xlsx_file)
sheet = wb_obj.active

col_names = []
for column in sheet.iter_cols(1, sheet.max_column):
    col_names.append(column[0].value)
   
    
print(col_names)

Upvotes: 0

Views: 855

Answers (1)

scotscotmcc
scotscotmcc

Reputation: 3113

Using the pandas library, I think we can achieve what you want like this:

import pandas as pd

df = pd.read_excel(path) # read our dataframe from excel

all_statements = [] # initialize an empty list to append to
for row in df.itertuples(index=False): # loop over each row
    statement = f"update system.user set meal_name = '{row.Meal}', meal_price = '{row.Child}' where customer_id = '{row.Id}'" # create a statement for that row using f-strings
    all_statements.append(statement) # append the statement for this row to our list of all_statements
    
with open(my_text_file, 'a') as file: # open in append mode, which will add new lines to the end of an existing file
    file.writelines(all_statements)

This code will read in your excel table and then loop over the rows to create a statement for each row. I don't think we need to do any if-else for creating those statements, since every statement appears to be the same format - it has the same pieces but we replace a few words with the values from the table.

Then we write these lines to your text file. In the last piece here, the file is opened in 'a' which is for append, and so will append the lines to an existing file (or create a new file if it doesn't already exist). If you want to overwrite what is already in the file, you will just change the 'a' to 'w' (for write mode).

Upvotes: 1

Related Questions