How do I convert my Table into a Normalised structure?

Question

For my Game App, I have a table of Data (one of many)in CSV format. It currently has 40+ columns, one for each Profession (fighter, rogue, wizard etc), and skill_name.

Each row (40+) contains the skill name, and a String representing the Cost of that skill for each profession.

A snippet from the original .csv contains data as shown here:

Skill,Fighter,Thief,Rogue
Armor-Heavy,2,4,3
Armor-Light,1,2,1
Armor-Medium,2,3,2

To enter this into a normalised database, I should have a Profession List table, a Skill List table, and a skill_costs_by_profession table.

I have built and populated the skill list and profession list tables:

CREATE TABLE "skills" ("_id" INTEGER, "skill" TEXT)

CREATE TABLE "professions" ("_id" INTEGER, "profession" TEXT)

I can create a table to hold the normalised data:

CREATE TABLE "skillCostByProfession" ("skill_id" INTEGER, "profession_id" INTEGER, "cost" INTEGER)

skillCostByProfession will have the data organised:

profession_id, skill_id, cost
1            |   1     |   2         (Fighter, Armor-Heavy, cost (2))
1            |   2     |   1         (Fighter, Armor-Light, cost (1))
1            |   3     |   2         (Fighter, Armor-Medium, cost (2))
2            |   1     |   4         (Thief, Armor-Heavy, cost (4))
2            |   2     |   2         (Thief, Armour-Light, cost (2))
2            |   3     |   3         (Thief, Armour-Medium, cost (3))
3            |   1     |   3         (Rogue, Armor-Heavy, cost (3))
3            |   2     |   1         (Rogue, Armour-Light, cost (1))
3            |   3     |   2         (Rogue, Armour-Medium, cost (2))

etc

I am struggling to find a way to put the data from the original .csv into the new skillCostByProfession table

I am using a combination of LibreOffice Calc and DB Browser (SQLite), to prepare the database for my Android App.

How can I convert the table of data I have into the format that I need, without writing the 1600 rows by hand?

Answer 1

I took it as a personal chalange and wrote this pseudocode so you could adapt it:

public Dictionary professionsToID = new Dictionary<string, string>();

public  Dictionary skillsToID = new Dictionary<string, string>();

public void Main(string[] args)
{
        string newcsvfile = "path\tempfilename.csv";
        Dictionary d = prepare();
        WriteDictionaryToFile(newcsvfile,d);


        AddSkillsToDatabase("source.csv", CreateConnection())
        skillsToID = GetSkillsId();

        AddProfessionToDatabase("source.csv", CreateConnection())
        professionsToID = GetProfessionId();


        TextToReferences("source.csv", "destination.csv");
}

public Dictionary prepare()
{

    // Reading textfile line by line
    https://stackoverflow.com/questions/5868369/how-to-read-a-large-text-file-line-by-line-using-java

    file = csv;

    List<String[]> actualCSVList = new List<String[]>
    String[][] actualCSVArr = new String[][]();


    try (BufferedReader br = new BufferedReader(new FileReader(file))) {
        String line;
        while ((line = br.readLine()) != null)
        {
            // process the line.

            //fill columnvalues in an array wich represents a row
            String[] arrOfRow = line.split(",");

            //Add row to List
            actualCSVList.Add(arrOfRow);
        }

        //convert list to multidimensional array
        actualCSVArr = actualCSVList.toArray();


        for(int d1 = 0; d1 <= actualCSVList.count-1, d1++)
        {
            //loop lines

            String[] actualLine = actualCSVArr[d1];
            Dictionary <String, String> rows = new Dictionary <String, String>();

            string skill = "";
            string prof = "";


            for(int i = 0; i <= actualLine.lenght; i++)
            {
                //replace index with profession (or skill)

                switch(i)
                {
                    case 0:
                        skill = actualLine[i].ToString();
                    break;

                    case 1:
                        prof = "Fighter";
                    break;

                    case 2:
                        prof = "Thief";
                    break;

                    case 3:
                        prof = "Rogue";
                    break;
                }

                //is the case when i = 0
                if(!prof.Equals(""))
                {
                    // Add: profession, skill, cost => "profession|arrayindex","skill:cost"
                    rows.Add(prof + "|" + i.ToString(), skill + ":" + actualLine[i].ToString());
                }
            }
        }

    return rows;
}

void WriteDictionaryToFile(string filename, Dictionary dic)
{
    //Write to the new CSV
    foreach (KeyValuePair<string,string>  kvp in dic)
    {
        string prof = kvp.Key.Split("|")[0];

        string[] skillAndCost = kvp.Value.Split(":");

        string skill = skillAndCost[0];
        string cost = skillAndCost[1];

        string nline = prof + "," + skill + "," + cost;

        System.IO.File.WriteNewLine(filename, nline);
    }
}

void TextToReferences(string sourcefile, string destinationfile)
{
    // Reading textfile line by line
    https://stackoverflow.com/questions/5868369/how-to-read-a-large-text-file-line-by-line-using-java

    try (BufferedReader br = new BufferedReader(new FileReader(sourcefile)))
    {
        String line;
        while ((line = br.readLine()) != null)
        {
            // process the line.

            // 0=prof 1=skill 2=cost
            string[] arrline = line.Split(",");
            string prof = arrline[0];
            string skill = arrline[1];
            string cost = arrline[2];

            string nline = professionsToID[prof] + "," + skillsToID[skill] + "," + cost;

            System.IO.File.WriteNewLine(destinationfile, nline));
        }
     }
}

Dictionary GetSkillsId()
{
    //https://www.codeguru.com/csharp/.net/net_data/using-sqlite-in-a-c-application.html

    Dictionary dic = new Dictionary();
    SQLiteConnection sqlite_conn;
    sqlite_conn = CreateConnection();
    List<string> columnnames = ReadColumnnames(sqlite_conn);


    SQLiteDataReader sqlite_datareader;
    SQLiteCommand sqlite_cmd;

    foreach (string column in columnnames)
    {
        sqlite_cmd = conn.CreateCommand();
        sqlite_cmd.CommandText = "SELECT id FROM skills WHERE skill = column";

        sqlite_datareader = sqlite_cmd.ExecuteReader();
        while (sqlite_datareader.Read())
        {
            string id = sqlite_datareader.GetString(0);
            dic.Add(column,id);
        }
    }
    conn.Close();


    return dic;
}

Dictionary GetProfessionsId()
{
    //https://www.codeguru.com/csharp/.net/net_data/using-sqlite-in-a-c-application.html

    Dictionary dic = new Dictionary();
    SQLiteConnection sqlite_conn;
    sqlite_conn = CreateConnection();
    List<string> columnnames = ReadColumnnames(sqlite_conn);


    SQLiteDataReader sqlite_datareader;
    SQLiteCommand sqlite_cmd;

    foreach (string column in columnnames)
    {
        sqlite_cmd = conn.CreateCommand();
        sqlite_cmd.CommandText = "SELECT id, profession FROM professions";

        sqlite_datareader = sqlite_cmd.ExecuteReader();
        while (sqlite_datareader.Read())
        {
            string id = sqlite_datareader.GetString(0);
            string pro = sqlite_datareader.GetString(1);
            dic.Add(pro,id);
        }
    }
    conn.Close();


    return dic;
}

static SQLiteConnection CreateConnection()
{

    SQLiteConnection sqlite_conn;
    // Create a new database connection:
    sqlite_conn = new SQLiteConnection("Data Source=
    database.db;Version=3;New=True;Compress=True;");
    // Open the connection:
    try
    {
        sqlite_conn.Open();
    }
    catch (Exception ex)
    {

    }

    return sqlite_conn;
}

static List<string> ReadColumnnames(SQLiteConnection conn)
{
    //https://stackoverflow.com/questions/685206/how-to-get-a-list-of-column-names/685212

    List<string> columnnames = new List<string>();

    SQLiteDataReader sqlite_datareader;
    SQLiteCommand sqlite_cmd;
    sqlite_cmd = conn.CreateCommand();
    sqlite_cmd.CommandText = "SELECT sql FROM sqlite_master WHERE tbl_name = 'table_name' AND type = 'table'";

    sqlite_datareader = sqlite_cmd.ExecuteReader();
    while (sqlite_datareader.Read())
    {
    string myreader = sqlite_datareader.GetString(0);
    columnnames.Add(myreader);
    }
    conn.Close();

    return columnnames;
}

static SQLiteConnection CreateConnection()
{

    SQLiteConnection sqlite_conn;
    // Create a new database connection:
    sqlite_conn = new SQLiteConnection("Data Source=
    database.db;Version=3;New=True;Compress=True;");
    // Open the connection:
    try
    {
    sqlite_conn.Open();
    }
    catch (Exception ex)
    {

    }
    return sqlite_conn;
}

static void AddSkillsToDatabase(string sourcefile, SQLiteConnection conn)
{
    // Reading textfile line by line
    https://stackoverflow.com/questions/5868369/how-to-read-a-large-text-file-line-by-line-using-java

    list<string> skills = new list<string>();

    try (BufferedReader br = new BufferedReader(new FileReader(sourcefile)))
    {
        String line;
        while ((line = br.readLine()) != null)
        {
            // process the line.

            // 1=skill
            string[] arrline = line.Split(",");
            string skill = arrline[0];

            if(!skills.Contains(skill))
            {
                skills.Add(skill);
            }
        }
     }


    SQLiteCommand sqlite_cmd;
    sqlite_cmd = conn.CreateCommand();

    foreach(string s in skills)
    {
        sqlite_cmd.CommandText = "INSERT INTO skills(skill) VALUES (s);";
        sqlite_cmd.ExecuteNonQuery();
    }

}

static void AddProfessionToDatabase(string sourcefile, SQLiteConnection conn)
{
    // Reading textfile line by line
    https://stackoverflow.com/questions/5868369/how-to-read-a-large-text-file-line-by-line-using-java

    list<string> professions = new list<string>();

    try (BufferedReader br = new BufferedReader(new FileReader(sourcefile)))
    {
        String line;
        while ((line = br.readLine()) != null)
        {
            // process the line. Firstline are the professions

            string[] arrline = line.Split(",");

            foreach(string p = arrline)
            {
                professions.Add(p);
            }

            //TODO: remove firts entry

            break;
        }
     }


    SQLiteCommand sqlite_cmd;
    sqlite_cmd = conn.CreateCommand();

    foreach(string p in professions)
    {
        sqlite_cmd.CommandText = "INSERT INTO professions(profession) VALUES (p);";
        sqlite_cmd.ExecuteNonQuery();
    }

}

The solution to a good database is a good concept. To ensure this you should always plan the database before you starting with coding it (i didn't do that everytime myself :D).

1. plan functionalities, windows and more
2. plan the database and ensure that you have all the values you need for the objects in step 1
3. create the database
4. Create queries wich you need for the planed objects from step 1
5. realize objects of step 1 and check if database and objects "match"

As an important starting point to plan the database you can create a diagram/scheme

For the scheme above i used Microsoft Visio (type "UML" in the Templatesearchbox and choose "UML-Datanotation"). You can find a lot of tools just search for "UML Tools" then you can search for the one wich matches your specifications. You should look for a tool wich support your programming language because that ones mostly have nice features fitted on the language (f.e. design database diagram => create programmcode).

A few tools are:

• Microsoft Visio (only for drawing)
• modelio
• Visual Paradigm

(@all readers: can you add some tools?)

Maybe your IDE have some diagram features as well.

But you should take a look on UML (Unified Modeling Language) first. Then you can decide wich program draw it like you like it most (or in the worst case draw diagrams by hand - sometimes its usefull if travel and have an idea).

Answer 2

A friend wrote me some python code, which does (almost) what I need.

filein=open("skills.csv","r")
fileout=open("out.txt","w")
professionline = filein.readline()
professions = professionline.rstrip().split(",")
for line in filein.readlines()[1:]:
    fields=line.rstrip().split(",")
    skill=fields[0]
    i=1
    for field in fields[1:]:
        cost=field
        if cost == "":
            cost = '0'      

        fileout.write("INSERT INTO skillCosts (skill_id, profession_id, cost) VALUES ((SELECT _id FROM skills WHERE category=\"{}\"), (SELECT _id FROM professions WHERE profession=\"{}\"), {});\n".format(skill, professions[i], cost))
        i+=1

This loads the .csv, and converts the top line (Profession Headers) into an array.
It then takes each row of Costs and creates an SQL Query.
This set of queries is written to a file (this example uses .txt, where I can copy/paste into DB Browswer's sql-input section)
It is possible to format a single Query to INSERT data, but my table goes over the 1,000 item limit.

Answer 3

First, I’d like to say that’s your chosen solution would be the best.

I think you could solve it like this:

(The columns “light”, “medium”, “heavy” are to check if the “level” is available – so you don’t have to create them if every skill has this three “levels”)

(Light, Medium, Heavy => content would be costs of “levels”)