Sign in to follow this  
Followers 0
MacScript

Merging some text database files together

5 posts in this topic

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 :)

 

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by TheSaint

AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

Make sure brain is in gear before opening mouth!
Remember, what is not said, can be just as important as what is said.

Spoiler

What is the Secret Key? Life is like a Donut

If I put effort into communication, I expect you to read properly & fully, or just not comment.
Ignoring those who try to divert conversation with irrelevancies.
If I'm intent on insulting you or being rude, I will be obvious, not ambiguous about it.
I'm only big and bad, to those who have an over-active imagination.

I may have the Artistic Liesense ;) to disagree with you. TheSaint's Toolbox (be advised many downloads are not working due to ISP screwup with my storage)

userbar.png

Share this post


Link to post
Share on other sites

MacScript,

Can you post several "Items" of actual data?


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

Here are several real records 

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"
    },
}

Share this post


Link to post
Share on other sites

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.


I am just a hobby programmer, and nothing great to publish right now.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0