Seed data from Excel to SQL insert query with minimal effort
Photo by Sunder Muthukumaran on Unsplash
If you have always tried to manually seed your data from excel to SQL insert query database then you would love this Python script more than anything.
Imagine you had to seed 100 plus records it would take around 30 minutes depending on your speed and accuracy.
All this can be easily automated with little help from Python.
Let’s consider this Excel data to be seeded into a SQL database.
Data in Excel
Converting Excel to SQL insert you must first learn to use the openpyxl library in Python. You can fetch cell values by passing in the row number and column alphabet. This way you can have more control over what you need to seed and how much.
Below is the script that will help to do that.
Hosted on my Github repo
Code explanation
Let me explain to you what the code does.
Line 1–12: I have used class to store data for better readability.
Line 14–15: Loading workbook into a variable and accessing a sheet
Line 18: List comprehension to get cell values from the row having column headers.
Line 19: List comprehension to list of cell values from start to end data list range.
Line 20–34: Logic to store the insert value as a string and its values. Now if values are string and simply add inverted commas if not ignore it. One weird thing about accessing such values is all int values added in Excel which be converted to float ie .0 will be added at the thing. I have added the logic to remove that as well so that the SQL script created would have no issues ie Line 28–30.
Line 35–36: I’ll just add the complete string into patch.sql
For seeding data into object
Considering the above data you will put the workbook name, and sheet name first.
Then, you will put 1 as colNamesRowNum as it contains your column headers.
Then, you simply you will put A2 in fromRange and C5 in as it is the range of your data.
That’s it you will be able to this do with any workbook and sheet from now.
You can enhance the above script to use a procedure instead of insert query.
Note: To learn more about this library you can visit.