Python Forum
Importing data from a text file into an SQLite database with Python
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Importing data from a text file into an SQLite database with Python
#1
Hi,

I have the following text file:
... here's a fragment

Output:
2020-05-22##18:00:00###RANGE ###RANGE ###RANGE ##201828##190182##96136##2 ##0 ###RANGE ###RANGE ##532387##644##313415 2020-05-22##19:00:00###RANGE ###RANGE ###RANGE ##201833##190185##96138##2 ##0 ###RANGE ###RANGE ##532387##644##313421 2020-05-22##20:00:00###RANGE ###RANGE ###RANGE ##201839##190191##96140##2 ##0 ###RANGE ###RANGE ##532387##644##313427 2020-05-22##21:00:00###RANGE ###RANGE ###RANGE ##201844##190195##96142##2 ##0 ###RANGE ###RANGE ##532387##644##313433 2020-05-22##22:00:00###RANGE ###RANGE ###RANGE ##201850##190201##96144##2 ##0 ###RANGE ###RANGE ##532387##644##313441 2020-05-22##23:00:01###RANGE ###RANGE ###RANGE ##201858##190207##96146##2 ##0 ###RANGE ###RANGE ##532387##644##313452 2020-05-23##00:00:00###RANGE ###RANGE ###RANGE ##201864##190212##96148##2 ##0 ###RANGE ###RANGE ##532387##644##313458 2020-05-23##01:00:00###RANGE ###RANGE ###RANGE ##201866##190215##96150##2 ##0 ###RANGE ###RANGE ##532387##644##313464 2020-05-23##02:00:00###RANGE ###RANGE ###RANGE ##201870##190217##96152##2 ##0 ###RANGE ###RANGE ##532387##644##313469 2020-05-23##03:00:00###RANGE ###RANGE ###RANGE ##201872##190220##96154##2 ##0 ###RANGE ###RANGE ##532387##644##313474 2020-05-23##04:00:00###RANGE ###RANGE ###RANGE ##201873##190221##96156##2 ##0 ###RANGE ###RANGE ##532387##644##313477 2020-05-23##05:00:00###RANGE ###RANGE ###RANGE ##201876##190223##96156##2 ##0 ###RANGE ###RANGE ##532387##644##313480 2020-05-23##06:00:00###RANGE ###RANGE ###RANGE ##201877##190224##96158##2 ##0 ###RANGE ###RANGE ##532387##644##313483 2020-05-23##07:00:01###RANGE ###RANGE ###RANGE ##201879##190226##96160##2 ##0 ###RANGE ###RANGE ##532387##644##313486 2020-05-23##08:00:00###RANGE ###RANGE ###RANGE ##201881##190228##96162##2 ##0 ###RANGE ###RANGE ##532387##644##313490 2020-05-23##09:00:01###RANGE ###RANGE ###RANGE ##201886##190233##96164##2 ##0 ###RANGE ###RANGE ##532387##644##313496 2020-05-23##10:00:00###RANGE ###RANGE ###RANGE ##201893##190238##96166##2 ##0 ###RANGE ###RANGE ##532387##644##313503 2020-05-23##11:00:00###RANGE ###RANGE ###RANGE ##201901##190246##96168##2 ##0 ###RANGE ###RANGE ##532387##644##313517 2020-05-23##12:00:00###RANGE ###RANGE ###RANGE ##201908##190253##96170##2 ##0 ###RANGE ###RANGE ##532387##644##313528 2020-05-23##13:00:00###RANGE ###RANGE ###RANGE ##201916##190259##96172##2 ##0 ###RANGE ###RANGE ##532387##644##313541 2020-05-23##14:00:00###RANGE ###RANGE ###RANGE ##201921##190265##96174##2 ##0 ###RANGE ###RANGE ##532387##644##313550 2020-05-23##15:00:00###RANGE ###RANGE ###RANGE ##201929##190272##96176##2 ##0 ###RANGE ###RANGE ##532387##644##313560 2020-05-23##16:00:00###RANGE ###RANGE ###RANGE ##201934##190278##96178##2 ##0 ###RANGE ###RANGE ##532387##644##313566 2020-05-23##17:00:00###RANGE ###RANGE ###RANGE ##201941##190283##96180##2 ##0 ###RANGE ###RANGE ##532387##644##313571 2020-05-23##18:00:00###RANGE ###RANGE ###RANGE ##201947##190289##96182##2 ##0 ###RANGE ###RANGE ##532387##644##313579 2020-05-23##19:00:00###RANGE ###RANGE ###RANGE ##201955##190295##96184##2 ##0 ###RANGE ###RANGE ##532387##644##313586 2020-05-23##20:00:01###RANGE ###RANGE ###RANGE ##201962##190301##96186##2 ##0 ###RANGE ###RANGE ##532387##644##313597 2020-05-23##21:00:00###RANGE ###RANGE ###RANGE ##201975##190312##96188##2 ##0 ###RANGE ###RANGE ##532387##644##313613 2020-05-23##22:00:00###RANGE ###RANGE ###RANGE ##202006##190326##96191##2 ##0 ###RANGE ###RANGE ##532387##644##313644 2020-05-23##23:00:00###RANGE ###RANGE ###RANGE ##202021##190341##96193##2 ##0 ###RANGE ###RANGE ##532387##644##313671 2020-05-24##00:00:00###RANGE ###RANGE ###RANGE ##202039##190355##96195##2 ##0 ###RANGE ###RANGE ##532387##644##313693 2020-05-24##01:00:00###RANGE ###RANGE ###RANGE ##202054##190369##96197##2 ##0 ###RANGE ###RANGE ##532387##644##313711 2020-05-24##02:00:00###RANGE ###RANGE ###RANGE ##202065##190379##96199##2 ##0 ###RANGE ###RANGE ##532387##644##313731 2020-05-24##03:00:00###RANGE ###RANGE ###RANGE ##202072##190386##96201##2 ##0 ###RANGE ###RANGE ##532387##644##313744 2020-05-24##04:00:00###RANGE ###RANGE ###RANGE ##202077##190390##96203##2 ##0 ###RANGE ###RANGE ##532387##644##313749 2020-05-24##05:00:00###RANGE ###RANGE ###RANGE ##202079##190392##96205##2 ##0 ###RANGE ###RANGE ##532387##644##313754 2020-05-24##06:00:00###RANGE ###RANGE ###RANGE ##202082##190394##96207##2 ##0 ###RANGE ###RANGE ##532387##644##313757 2020-05-24##07:00:00###RANGE ###RANGE ###RANGE ##202083##190397##96209##2 ##0 ###RANGE ###RANGE ##532387##644##313761 2020-05-24##08:00:00###RANGE ###RANGE ###RANGE ##202087##190399##96211##2 ##0 ###RANGE ###RANGE ##532387##644##313766 2020-05-24##09:00:00###RANGE ###RANGE ###RANGE ##202092##190403##96213##2 ##0 ###RANGE ###RANGE ##532387##644##313772 2020-05-24##10:00:00###RANGE ###RANGE ###RANGE ##202098##190410##96215##2 ##0 ###RANGE ###RANGE ##532387##644##313780
I wonder if it is easy to create a database directly from a text file?
Reply
#2
what does ###RANGE signify?
is this file tab delimited?
Reply
#3
this text file is a reading from external sensors, some sensors are out of order, so it is "### RANGE". I would like to write a script in Python that will create a database from this file without "### RANGE" and "##".
Reply
#4
So what have you tried?
spam ='2020-05-22##18:00:00###RANGE ###RANGE ###RANGE ##201828##190182##96136##2  ##0  ###RANGE ###RANGE ##532387##644##313415'
print(spam.split('##'))
Output:
['2020-05-22', '18:00:00', '#RANGE ', '#RANGE ', '#RANGE ', '201828', '190182', '96136', '2 ', '0 ', '#RANGE ', '#RANGE ', '532387', '644', '313415']
you may want to strip white spaces from some elements. As you can see you can get rid of #RANGE
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#5
in my previous post, the guys showed me a nice way, but how to load it into a database?

file = 'raport.txt'
 
array = []
with open(file, 'r') as lines:
    for line in lines:
        newlines = line.replace('###', ' ').replace('##', ' ').replace('RANGE', ' ').replace('\n', ' ').split()
        array.append(newlines)
 
    for line in array:
        print(line)
Reply
#6
what I show you - is better. Obviously separator is '##'.
spam ='2020-05-22##18:00:00###RANGE ###RANGE ###RANGE ##201828##190182##96136##2  ##0  ###RANGE ###RANGE ##532387##644##313415'
eggs = [ele.strip() for ele in spam.split('##') if not ele.startswith('#RANGE')]
print(eggs)
Output:
['2020-05-22', '18:00:00', '201828', '190182', '96136', '2', '0', '532387', '644', '313415']
It's not clear if there could be case where instead of '#RANGE' you have a valid value and you need to handle such case.

for sqlite look at sqlite3 module
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#7
...and what would it look like with the creation of the database?
Reply
#8
(Jun-29-2020, 08:32 PM)macieju1974 Wrote: ...and what would it look like with the creation of the database?
did you look at the link? The answer is right at the top of the link I shared.
Of course, you can create the db in advance using one of many available GUI tools to work with sqlite and only use the file already created in your script.
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  need help with data analysing with python and sqlite Hardcool 2 299 Jan-30-2024, 06:49 AM
Last Post: Athi
  Replace a text/word in docx file using Python Devan 4 2,854 Oct-17-2023, 06:03 PM
Last Post: Devan
  python sqlite autoincrement in primary column janeik 6 1,075 Aug-13-2023, 11:22 AM
Last Post: janeik
  save values permanently in python (perhaps not in a text file)? flash77 8 1,120 Jul-07-2023, 05:44 PM
Last Post: flash77
  How to detect abnormal data in big database python vanphuht91 5 1,064 Jun-27-2023, 11:22 PM
Last Post: Skaperen
  Database that can compress a column, or all data, automatically? Calab 3 1,120 May-22-2023, 03:25 AM
Last Post: Calab
Thumbs Up Need to compare the Excel file name with a directory text file. veeran1991 1 1,064 Dec-15-2022, 04:32 PM
Last Post: Larz60+
  New2Python: Help with Importing/Mapping Image Src to Image Code in File CluelessITguy 0 698 Nov-17-2022, 04:46 PM
Last Post: CluelessITguy
  python standard way of importing library mg24 1 872 Nov-15-2022, 01:41 AM
Last Post: deanhystad
  Basic SQL query using Py: Inserting or querying sqlite3 database not returning data marlonbown 3 1,304 Nov-08-2022, 07:16 PM
Last Post: marlonbown

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020