Import the library ElementTree required for XML parsing. glob gets the XML Path in the folder Pandas are stored in an array. Export from pandas to csv and save to MySQL.
from xml.etree import ElementTree
import glob
import pandas as pd
XML Analyze XML data like this. offset stores the time in seconds. 86400 seconds is 24 hours
-<Day>
-<EventCollection>
-<Event>
<Offset>0</Offset>
<Value>70</Value>
</Event>
-<Event>
<Offset>86400</Offset>
<Value>69</Value>
</Event>
-<Event>
<Offset>172800</Offset>
<Value>73</Value>
</Event>
<Day>
Gets and parses the path of the XML downloaded in the folder.
xmlfile = glob.glob("C:/Users/user/*") #Store XML file path in file
file = len(xmlfile) #Count the number of XML files
i_file = 0 #Number to specify the XML file in order from the top_0 is the very first
for i in range(file):
XMLFILE = xmlfile[i_file]
i_file += 1
tree = ElementTree.parse(XMLFILE) #Read XML file
root = tree.getroot() #Get the contents of XML
#prepare list
Day = []
Night = []
#Store all numbers in list using for
for e in root.findall('.//Day/EventCollection/Event/Value'):
Day.append(e.text)
for e in root.findall('.//Night/EventCollection/Event/Value'):
Night.append(e.text)
print(Day)
[70,69,73]
#Combine list into one
listData = [Day, Night]
#Convert listData to DataFrame..T swaps rows and columns
df = pd.DataFrame(listData).T
#Add column name
df.columns = ['Day', 'Night']
#Set csv path and save pandas DataFram to csv
filename = 'C:/Users/user/csv/AAA.csv'
df.to_csv(filename, index=False)
Save the DataFrame output by csv to MySQL as well.
Import library
from sqlalchemy import create_engine
url = 'mysql+mysqlconnector://[user]:[pass]@[host]:[port]/sampleDB'
engine = create_engine(url, echo=True)
#df.to_with sql'sampleDB_table'Save the data stored in df
df.to_sql('sampleDB_table', engine, index=False, if_exists='append')
I run this from FileMaker, but if it's Windows, if you run it regularly with a task scheduler etc., the data will be automatically stored in the database and you can use it for later analysis. In the future, I would like to work on data visualization (BI tool) using PHP, javascript, etc., and then machine learning (AI).
Recommended Posts