python sqlite3 creating a DB from excel

Question

I'm trying to create a DB from an excel spreadsheet. I have the below code, the issue is when i run my code my database creates a table for each column. I would like to create a table for each spreadsheet listed in the workbook. sheet names are sheet1 and sheet2.

import sqlite3
import pandas as pd
filename="script"
con=sqlite3.connect(filename+".db")
wb=pd.read_excel(filename+'.xlsx',sheet_name='sheet1')
for sheet in wb:
wb[sheet].to_sql(sheet,con, index=False)
con.commit()
con.close()

You're loading one sheet specifically via sheetname='sheet1' use sheetname=None to load them all, then loop over that — Jon Clements
– Jon Clements, Commented Oct 18, 2017 at 15:59
I originally tried sheetname=none but get the traceback NameError: name 'none' is not defined — springcj1
– springcj1, Commented Oct 18, 2017 at 16:17
per havlock below and stackoverflow.com/questions/57348149. Updated code to reflect sheet_name vs sheetname to avoid a second error. — springcj1
– springcj1, Commented Apr 29, 2021 at 20:56

Jon Clements · Accepted Answer · 2020-01-07 14:05:04Z

10

Passing sheetname=None will give you an OrderedDict with keys of the sheet name and values as dataframes, you then loop over that.

import pandas as pd
import sqlite3

db = sqlite3.connect(':memory:')
dfs = pd.read_excel('somefile.xlsx', sheet_name=None)
for table, df in dfs.items():
    df.to_sql(table, db)

edited Jan 7, 2020 at 14:05

answered Oct 18, 2017 at 16:20

Jon Clements

143k34 gold badges254 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

springcj1 Over a year ago

Thank you passing sheetname=None is what i was looking for. I originally tried that but was hung up as i was passing "none" instead of "None" rookie mistake i appreciate the help!

havlock Over a year ago

Per stackoverflow.com/questions/57348149 sheetname should (now?) be sheet_name, ie with an underscore

SkyWalker Over a year ago

The column names? header or no header?

user16812747 Over a year ago

Sir after so many year's I'm asking how can we insert into custom table having custom colmuns?

Ashok Philip · Accepted Answer · 2017-10-18 17:33:56Z

3

import sqlite3
import pandas as pd
filename="script"
con=sqlite3.connect(filename+".db")
wb=pd.ExcelFile(filename+'.xlsx')
for sheet in wb.sheet_names:
        df=pd.read_excel(filename+'.xlsx',sheetname=sheet)
        df.to_sql(sheet,con, index=False,if_exists="replace")
con.commit()
con.close()

Get the sheetnames first and then read the sheets and write to sqlite as given above.

answered Oct 18, 2017 at 17:33

Ashok Philip

913 bronze badges

1 Comment

Kokokoko Over a year ago

Update: sheetname = sheet has changed to sheet_name = sheet as sheetname got deprecated as of 2018.

Collectives™ on Stack Overflow

python sqlite3 creating a DB from excel

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related