Manage data from .txt file to store it to SQLite3 in Python

Question

I need to store the data from a .txt file to a database in Sqlite3.

I first try to read the txt file:

f = open("path", 'r')
if f.mode=='r':
    content = f.read()

then i printed the "content" to know the structure of the data

print (content)

Rank   Male     Female 
1       Noah    Emma
2       Liam    Olivia
3       William Ava
4       Mason   Sophia
5       James   Isabella

How can i manage the data from the variable "content" to store it in a database with a table separated as rank, name and sex.

Please make some attempt at solving this yourself. There are plenty of guides available if you search online. Your code makes no use of `sqlite3` at all. — roganjosh, May 25 '18 at 19:29
@IvanVinogradov CSV files are already list when you open them. TXT files are string — Andres ZW, May 25 '18 at 19:33

Ivan Vinogradov · Accepted Answer · 2022-09-04T08:22:25.033

If you insist on manually inserting data from a text file, or you have no idea about what delimiters there are, you can do something like this:

import sqlite3
    
# create in-memory db and connect
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE t (col1, col2, col3);")  # use your column names here
    
# read data from file
f = open('<YOUR_FILE.TXT>', 'r')
cont = f.read()
f.close()
    
# format for inserting to db
rows = cont.split('\n')
rows = rows[1:]  # delete first row with captions
formatted = [tuple(x.split()) for x in rows]
    
# insert into db
cur.executemany("INSERT INTO t (col1, col2, col3) VALUES (?, ?, ?)", formatted)
con.commit()
con.close()

Manage data from .txt file to store it to SQLite3 in Python

1 Answers1