How to make a local database for android studio and immediately fill it with data from csv or xml?

Question

Android

How to make a local database for android studio and immediately fill it with data from csv or xml?

Answer 1

Install Python , PyCharm (trial version). create a project there, transfer this file there:
href = https://yadi.sk/d/A6_iVd1QY5HdRA
set the encoding

then, in the Database tab, create an SQlite database (or open a .db file of another database), right-click to import the csv file , which is already encoded and done as in the image below

and that's it, the completed database is ready,

be sure to write id (create a field in each table) with autoincrement and Primary Key (PK)
you can also make links (automatically create a table)
linking example one-to-many tables.
1. create table phone_test, set id (autoincrement, PK)

2. go to the first table and modify the home_telephone column, it
should turn out like this:

everything, two tables are connected, in the console tab we write a test query

example of a Foreign Key query One to many

SELECT pt.id, pt.phone, pt.column_2 FROM TemplateImportEmpl LEFT JOIN phone_test pt on pt.id = TemplateImportEmpl.home_telephone

see the result

If you need to make a CSV table for any database from a complex XML file.
let's say there is such an XML file and you need to make a CSV table

<?xml version="1.0" encoding="UTF-8"?>
<services>
    <strategy odn="149" name="kv" date="2020-11-18">
        <post>eVoting Booth</post>
        <inf>
            <tag id="10" er="er1"/>
            <tag id="14" er="er1"/>
            <tag id="11" er="er2"/>
            <tag id="12" er="er3"/>
            <tag id="13" er="er4"/>
        </inf>
    </strategy>
    <strategy odn="150" name="vk" >
        <post>Library</post>
        <inf>
            <tag id="10" er="er1"/>
            <tag id="13" er="er4"/>
        </inf>
    </strategy>
</services>

here is the code to create a table with pulling out tags and filling in gaps in XML

from lxml import etree

import pandas as pd


trree = etree.parse("input_file.xml")

table = trree.xpath('/services/strategy/inf/tag')
tableoutput = []
for i in table:
    ig = i.xpath('./@er') #доступ к тегам и распечатка тегов, доступ к post
    tableoutput+= ig
print("поля в будущей таблице: ", set(tableoutput), "сменить таблицу (экс)")

id = []
counttag = 0
minsec = []
minseccel = []
counter1musttwo = []
tagnull = []
identifie = trree.xpath('/services/strategy/inf')
for c in identifie:
    counttag +=1
    ct = c.xpath('./tag')
    print ("количество тегов в",counttag, "inf: ", len(ct))
    if (len(ct) == 0):
        tagnull += "TAG = 0; inf №", counttag
        print("найден inf с Количеством тегов = 0")
    for c1 in ct:
        ct1 = c1.xpath('./@er')
        ct2 = c1.xpath('./@id')
        print("распечатываем er: ", ct1)
        if (ct1 == ['er1']):
            id += ct2
        minsec += ct1
        minseccel += ct1
    print("кол-во er1 в",counttag, "inf: ", minsec.count("er1"))
    if (minsec.count("er1") >= 2):
        counter1musttwo += "er1 > 2; inf №",counttag, "; кол-во er1", minsec.count("er1")
        print("найден inf с er1 больше 2")
    print("   \n ")
    minsec = []

identifie = trree.xpath('/services/strategy')
for re in identifie:
    reos = re.xpath('./post/text()')
    print("доступ к post", reos)
#выше мы подсчитали все элементы tag
#делаем пример таблицы csv если каких то полей не хватает поле дата не постоянно
print("\n")
strategy = trree.xpath('/services/strategy')
squares = []
for rv in strategy:
    noner = rv.xpath('./@date')

    if (noner == []):
        noner = ['not found']
    squares += noner
print(squares)
postoutput = []
for re in strategy:
    reos = re.xpath('./post/text()')
    if (reos == []):
        reos = ['not found']
    postoutput+= reos
print(postoutput)

id = trree.xpath('/services/strategy/@odn')
name = trree.xpath('/services/strategy/@name')

df = pd.DataFrame({
    "Id": id,
    "Name": name,
    "Data": squares,
    "Post": postoutput
})

df.to_csv("out.csv", sep="|", index=None)
#файл готов

in console:

Answer 2

David Griffiths, Don Griffiths - Head First Android Programming , chapter 11.