[Tutor] make a sqlite3 database from an ordinary text file

[Alan Gauld]

On 20/06/2022 01:59, Manprit Singh wrote:

> As i wrote in the starting , i have a simple text file named abcd.txt. (I
> am not using a csv file or any other structured file) . The contents are
> given below :
> 
> Time  Pieces
> 1       10
> 2       15
> 3       25
> 4       31
> 5       40
> 6       45
> 7       53
> 8       54
> 9       65
> 10      75

Sticking with the theme of using the right tool for the right
job here is how I did this on Unix(after cutting and pasting
your daata into a file called data.txt):

~/src/sql $ tr -cs "[:alnum:]\n" "," <data.txt >data2.txt
~/src/sql $ sqlite3

sqlite> .import -csv data2.txt DATA

sqlite> .schema DATA
CREATE TABLE IF NOT EXISTS "DATA"(
  "Time" TEXT,
  "Pieces" TEXT
);

sqlite> Select Time from DATA where Pieces=40;
5

ie.
I used tr to convert the text file to a CSV.
(-cs means translate everything except alphanum and newlines
and "squeeze" to one replacement character)


Then I imported the csv file into SQLite.

To test it, I checked the schema create statement
and did a select query.

If you don't like tr you could of course use a simple python
(or perl or awk or sed) script to replace the spaces with , or
even use a spreadsheet such as Excel. In Python:

>>> with open('data.txt') as inf:
         with open('data2.txt",'w') as outf:
            for line in inf:
                line = re.sub(" +", ",", line)
                outf.write(line)


The only problem with this approach is that it makes the
two columns TEXT rather than INT.

-- 
Alan G
Author of the Learn to Program web site
http://www.alan-g.me.uk/
http://www.amazon.com/author/alan_gauld
Follow my photo-blog on Flickr at:
http://www.flickr.com/photos/alangauldphotos