vik123 vik123 - 1 year ago 105
MySQL Question

parsing and inserting raw text data into mysql database using python or shell

I have a text file which has data in the following format:
these are 2 lines from the text file which is ncdc raw weather data:
The highlighted part is the air temperature which will be (Degree Celsius *100) Thats just one of the columns that i need to insert into database.
The temperature position always remains the same.



How do i read this text file either in Python or Bash and insert into a table in mysql database:
mysql Ver 14.14 Distrib 5.5.52, for debian-linux-gnu (x86_64) using readline 6.3
Python 2.7.6
I'm running a vagrant ubuntu virtual machine and working on it!

for example the table name is myweatherdata


Answer Source

In python you could do something like:

f = open("data.txt", "r") #assuming this is your file
data =

for line in data.split("\n"): #split all data in lines 
    print line[88:93]

Last step would be inserting it in the DB instead of just printing it, eg:

INSERT INTO myweatherdata (temperature) 
VALUES (temperature_1)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download