SQL Insert Generator from Excel File using Python

1

pip install pandas click

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

import click
import errno
import os
import pandas as pd

@click.command()
@click.option('--generate', '-g', help='Change TEXT to generate excel file into SQL insert')
@click.option('--outputdir', '-o', help='Change TEXT to create directory output file')
def main(generate, outputdir):
	try:
		# Validate generate file can not be None
		if generate is None:
			raise TypeError

		# Check if outputdir is not None
		if outputdir != None:
			try:
				# Create a directory
			    os.makedirs(outputdir)
			    outputdir = "{}/".format(outputdir)
			except OSError as exc:
				# If directory is exists use this directory
				if exc.errno == errno.EEXIST:
					outputdir = "{}/".format(outputdir)
		file = pd.ExcelFile(generate)
		for sheet_name in file.sheet_names:
		    data = file.parse(sheet_name)
		    filename = "{}{}.sql".format(outputdir, sheet_name)
		    click.echo("### {}:".format(filename))
		    write_file = open(filename, "w")
		    for i, _ in data.iterrows():
		        field_names = ", ".join(list(data.columns))
		        rows = list()
		        for column in data.columns:
		            rows.append(str(data[column][i]))
		        row_values = "'" + "', '".join(rows) + "'"
		        click.echo("INSERT INTO {} ({}) VALUES ({});".format(sheet_name, field_names, row_values))
		        write_file.write("INSERT INTO {} ({}) VALUES ({});\n".format(sheet_name, field_names, row_values))
		    write_file.close()
	except TypeError as e:
		click.echo("Error: Unknown generate file! Type -h for help.")

if __name__ == "__main__":
    main()

1
2
3

INSERT INTO M_ACCOUNT (ID, FULLNAME, ADDRESS, IDENTITY_NUMBER, IDENTITY_TYPE, COUNTRY) VALUES ('1', 'John Doe', 'Yogyakarta', '34754354986', 'KTP', 'Indonesia');
INSERT INTO M_ACCOUNT (ID, FULLNAME, ADDRESS, IDENTITY_NUMBER, IDENTITY_TYPE, COUNTRY) VALUES ('2', 'Maverick', 'Jakarta', '43589743545', 'KTP', 'Indonesia');
INSERT INTO M_ACCOUNT (ID, FULLNAME, ADDRESS, IDENTITY_NUMBER, IDENTITY_TYPE, COUNTRY) VALUES ('3', 'Al Sah-Him', 'Semarang', '58479846645', 'KTP', 'Indonesia');

1
2
3

INSERT INTO M_USER (ID, USERNAME, PASSWORD, M_ACCOUNT_ID) VALUES ('1', 'johndoe', '$2y$12$tRgbrmjdyytEyv8ceakIc.7vUCjLfpEi6K/Ube0hB5X4c7vPcMMQC', '1');
INSERT INTO M_USER (ID, USERNAME, PASSWORD, M_ACCOUNT_ID) VALUES ('2', 'maverick', '$2y$12$tRgbrmjdyytEyv8ceakIc.7vUCjLfpEi6K/Ube0hB5X4c7vPcMMQC', '2');
INSERT INTO M_USER (ID, USERNAME, PASSWORD, M_ACCOUNT_ID) VALUES ('3', 'alsahhim', '$2y$12$tRgbrmjdyytEyv8ceakIc.7vUCjLfpEi6K/Ube0hB5X4c7vPcMMQC', '3');