Merging some text database files together

[MacScript]

I have a multiple text files which is an export of inventory data.  Each Text file represents a different system export.  All the data is in the same format.

[Item] =
    {
        ["Total"] = "21",
        ["SortCategory"] = "6",
        ["BackgroundImageID"] = "1091711664",
        ["Qty"] =
        {
            ["User1"] =
            {
                ["Subtotal"] = "1",
                ["5"] = "1"
            },
            ["User2"] =
            {
                ["Subtotal"] = "20",
                ["4"] = "20"
            }
        },
        ["IconImageID"] = "1091718003",
        ["Category"] = "6",
        ["Quality"] = "2"
    },

I am looking for a way to merge this information into one file. So every Item record would need to be merged together.  After all this information is merged I would like to be able to search it. So for instance I want to look for item X and then have it show me the quantities and the users who have item X.

My primary focus of this post is to see what is the best way to read in these files and merge them together. To be honest, I do not have any code yet. I have looked at this and been trying on paper to get a basic concept as to how to do this task but so far have not come up with one. Was not sure if there might be a function that already does this.

This information in theory is in a LUA table structure, but I am not familiar with LUA so can't confirm this.

Thanks for pointing me in a direction ahead of time

 

[TheSaint]

That data is in an unusual layout form (for me anyway), but I would read it in by separating each line by a carriage return, maybe using an Array .... and maybe counting leading tabs or if the bracketed sections are repeated for each item, then use them as keys, perhaps writing all results to an XML or SQL file ... or even INI file (if you can think your way around duplicate keys per item).

Have a look at the following functions.

_FileReadToArray

StringStripWS

StringSplit

_FileCreate

StringInStr

plus StringRegExp and other RegExp if you can handle them.

and the UDF's for XML or SQL and INI functions.

etc

Edited February 11, 2015 by TheSaint

[kylomas]

MacScript,

Can you post several "Items" of actual data?

[MacScript]

Here are several real records 

expandcollapse popup

return
{
    ["Flour"] =
    {
        ["SortCategory"] = "37",
        ["Total"] = "101",
        ["Category"] = "37",
        ["Qty"] =
        {
            ["18123-0111"] =
            {
                ["Subtotal"] = "81",
                ["5"] = "81"
            },
            ["17342-1212"] =
            {
                ["Subtotal"] = "20",
                ["4"] = "20"
            }
        },
        ["IconImageID"] = "1091444181",
        ["BackgroundImageID"] = "1090640816",
        ["Quality"] = "4"
    },
    ["Sugar"] =
    {
        ["SortCategory"] = "6",
        ["Total"] = "1",
        ["Category"] = "6",
        ["Qty"] =
        {
            ["18123-0111"] =
            {
                ["Subtotal"] = "1",
                ["5"] = "1"
            }
        },
        ["IconImageID"] = "1091718003",
        ["BackgroundImageID"] = "1091711664",
        ["Quality"] = "2"
    },
    ["Red Dye"] =
    {
        ["SortCategory"] = "57",
        ["Total"] = "23",
        ["Category"] = "57",
        ["Qty"] =
        {
            ["18123-0111"] =
            {
                ["Subtotal"] = "16",
                ["2"] = "16"
            },
            ["17342-1212"] =
            {
                ["1"] = "7",
                ["Subtotal"] = "7"
            }
        },
        ["IconImageID"] = "1091804780",
        ["BackgroundImageID"] = "1090519043",
        ["Quality"] = "3"
    },
    ["Corn Syrup"] =
    {
        ["SortCategory"] = "33",
        ["Total"] = "1",
        ["Category"] = "33",
        ["Qty"] =
        {
            ["19212-1212"] =
            {
                ["1"] = "1",
                ["Subtotal"] = "1"
            }
        },
        ["IconImageID"] = "1091768345",
        ["BackgroundImageID"] = "1090519043",
        ["Quality"] = "3"
    },
    ["Baking Soda"] =
    {
        ["Total"] = "1",
        ["SortCategory"] = "49",
        ["BackgroundImageID"] = "1090519044",
        ["Qty"] =
        {
            ["17342-1212"] =
            {
                ["Subtotal"] = "1",
                ["2"] = "1"
            }
        },
        ["IconImageID"] = "1091581771",
        ["Category"] = "49",
        ["Quality"] = "2"
    },
}

[Myicq]

MacScript, compare your data to JSON, you will see it's very similar.  http://json.org/example

Read all into a variable, then make some minor changes.

• Initial "return" gone
• ["Red Dye"]  should be   "Red Dye"  without []
• " = "   should be  " : "
• Final comma should not be there, as it would cause an empty JSON element

Then you can parse with JSON parsers. There are examples around here.