22

I have a excel sheet with full of data. I want same data in sqlite database table. Currently I have to manually enter 1 by 1 field. Is there any way to direct exporting data to sqlite database table?

Naveed Mansuri
  • 439
  • 1
  • 5
  • 13
  • Use SQLite Expert Professional 3 – Omid Omidi Oct 31 '14 at 08:55
  • 2
    Possible duplicate of [Import CSV to SQLite](http://stackoverflow.com/questions/14947916/import-csv-to-sqlite) – zero323 Jun 25 '16 at 19:15
  • Your path is through CSV (You should export your data to CSV), see more here for example. https://stackoverflow.com/questions/14947916/import-csv-to-sqlite – oz123 Jul 03 '13 at 05:13
  • There's also a python script ([xl2sqlite.py](https://gist.github.com/meitar/fb62f19aa1d73b766dbc)) that will do the job. – M12 Nov 05 '15 at 08:26
  • 1
    This site converts Excel files to sqlite files http://converttosqlite.com/ – Code Slinger Feb 02 '15 at 17:37
  • 2
    This is not a duplicate.. there is a significant difference importing xlsx and csv files. Excel exported csv's are all malformed and messed up so most of them is not usable for imports. Excel automatically re-formats data types as soon as you export the file to csv therefore direct import from xlsx would be an advantage. – Mick_ May 22 '18 at 10:54
  • 1
    Excel's cvs are UFT-16 little endian encoded, that's why you cannot import directly into sqlite. You need to convert it first to UTF-8. For this task, `iconv` is your friend. – Alejandro Blasco Oct 25 '19 at 17:45
  • This question is focused enough, legitimate problem, should be reopened – Rainb Mar 22 '22 at 13:31

1 Answers1

16

Try this fresh code exceltosql:

'''
This code uses the openpyxl package for playing around with excel using Python code
to convert complete excel workbook (all sheets) to an SQLite database
The code assumes that the first row of every sheet is the column name
Every sheet is stored in a separate table
The sheet name is assigned as the table name for every sheet
'''

import sqlite3
import openpyxl
from openpyxl import load_workbook
import re

def slugify(text, lower=1):
    if lower == 1:
        text = text.strip().lower()
    text = re.sub(r'[^\w _-]+', '', text)
    text = re.sub(r'[- ]+', '_', text)
    return text

#Replace with a database name
con = sqlite3.connect('test.db')
#replace with the complete path to youe excel workbook
wb = load_workbook(filename=r'abc.xlsx')

sheets = wb.get_sheet_names()

for sheet in sheets:
    ws = wb[sheet] 

    columns= []
    query = 'CREATE TABLE ' + str(slugify(sheet)) + '(ID INTEGER PRIMARY KEY AUTOINCREMENT'
    for row in ws.rows[0]:
        query += ', ' + slugify(row.value) + ' TEXT'
        columns.append(slugify(row.value))
    query += ');'

    con.execute(query)

    tup = []
    for i, rows in enumerate(ws):
        tuprow = []
        if i == 0:
            continue
        for row in rows:
            tuprow.append(unicode(row.value).strip()) if unicode(row.value).strip() != 'None' else tuprow.append('')
        tup.append(tuple(tuprow))


    insQuery1 = 'INSERT INTO ' + str(slugify(sheet)) + '('
    insQuery2 = ''
    for col in columns:
        insQuery1 += col + ', '
        insQuery2 += '?, '
    insQuery1 = insQuery1[:-2] + ') VALUES('
    insQuery2 = insQuery2[:-2] + ')'
    insQuery = insQuery1 + insQuery2

    con.executemany(insQuery, tup)
    con.commit()

con.close()
Tunaki
  • 132,869
  • 46
  • 340
  • 423
Shahzeb Qureshi
  • 604
  • 6
  • 11
  • 3
    Works nicelly, but needs changing "for row in ws.rows[0]:" to "for row in next(ws.rows):" in line 33. Thanks! – Zalakain Apr 04 '17 at 21:52