vik123 vik123 - 2 months ago 15
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.

0029227070999991901122820004+62167+030650FM-12+010299999V0200501N003119999999N0000001N9-01561+99999100061ADDGF108991999999999999999999

0029227070999991901122906004+62167+030650FM-12+010299999V0200901N003119999999N0000001N9-01501+99999100181ADDGF108991999999999999999999

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

Thanks!

Answer

In python you could do something like:

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

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)