Importing data from a text file into an SQLite database with Python - macieju1974 - Jun-29-2020
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?
RE: Importing data from a text file into an SQLite database with Python - Larz60+ - Jun-29-2020
what does ###RANGE signify?
is this file tab delimited?
RE: Importing data from a text file into an SQLite database with Python - macieju1974 - Jun-29-2020
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 "##".
RE: Importing data from a text file into an SQLite database with Python - buran - Jun-29-2020
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
RE: Importing data from a text file into an SQLite database with Python - macieju1974 - Jun-29-2020
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)
RE: Importing data from a text file into an SQLite database with Python - buran - Jun-29-2020
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
RE: Importing data from a text file into an SQLite database with Python - macieju1974 - Jun-29-2020
...and what would it look like with the creation of the database?
RE: Importing data from a text file into an SQLite database with Python - buran - Jun-29-2020
(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.
|