Last active
February 22, 2018 14:00
-
-
Save Gueka/be37538f11ff9f186654e9e8a0c4c597 to your computer and use it in GitHub Desktop.
I use this gist to parse large xlsx file to get the inserts for my database
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import xlrd | |
from tkinter import filedialog | |
from tkinter import Tk | |
import os | |
Tk().withdraw() # we don't want a full GUI, so keep the root window from appearing | |
print("Start .xlsx parser") | |
path = filedialog.askopenfilename(initialdir = os.getcwd(),title = "Select an spreedsheet to parse ",filetypes = (("Excel files","*.xlsx"),("all files","*.*"))) # show an "Open" dialog box and return the path to the selected file | |
print("Opening file: " + path) | |
workbook = xlrd.open_workbook(path) | |
print("File loaded successfully") | |
sheet_names = workbook.sheet_names() | |
print('Sheet Names: ', sheet_names) | |
selectedSheet = input("Select the sheet you want to parse: " + ' '.join(sheet_names) + "\n") | |
print('Parsing sheet: ', selectedSheet) | |
sheet = workbook.sheet_by_name(selectedSheet) | |
resultFileName = filedialog.askopenfilename(initialdir = os.getcwd(),title = "Where you want the result sql?",filetypes = (("Sql files","*.sql *.txt"),("all files","*.*"))) | |
print("Generating result file: " + resultFileName) | |
file = open(resultFileName,'w') | |
# Create a For loop to iterate through each row in the XLS file, starting at row 2 to skip the headers | |
for r in range(1, sheet.nrows): | |
#I know which cell represent each variable, so... | |
id = str(int(sheet.cell(r,0).value)) | |
id_event = str(int(sheet.cell(r,1).value)) | |
description = str(sheet.cell(r,2).value) | |
# Generate each insert | |
file.write("INSERT INTO DTV_CROSS_EVENT_REASON (ID, ID_EVENT, DESCRIPTION) values (" + id + ", " + id_event + ", '" + description + "');\n") | |
file.close() | |
print("Finish .xlsx parser") |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment