text based file import, database populate and then export.

[R2dical]

Hi everyone! (first post)

So we are working on a game and now have to edit a large number of text based files used to supply info to the engine. These have been done manually but now require balancing and a excel database will be perfect. But how to get them in correctly, then export when needed?

I have taken a shot at making some macros to do this but they ended up being very slow and there is probably a better way to do this. I used Jerry Beaucaires https://sites.google.com/a/madrocket...xcelassistant/ excellent scripts as a basis but think the step by step approach I used could be done much easier a different way.

The .weapon text files look like this:

weapon
{
    name            'PD Interceptor'
    weaponclass        pdmissile
    weaponfamily        missile
    model1            ""                // insert barrel model for tiny mounts here if we get them
    model2            barrel_pdmissile.X        // NOTE: model2 for tiny pd weapons in small mounts
    requires        WEP_PDtech
    requires        DRV_Ints
    turretsize        tiny
    cost            4000
    turretclass        standard
    burst_volleys        1
    volley_period        .2
    recharge_time        8
    muzzle_sound        Sounds/Weapons/mis_interceptor_pd_muzzle.wav
    muzzle_sound_minrange    60
    muzzle_effect        effects/Missile_dfire_muzzle.effect
    muzzle_speed        100
    solution_tolerance    360
    icon_file        GUI/WeaponIcons_Warhead.tga
    icon_rect        "128 192 64 64"

    range            700
    range_planet        0    // Same as 'range' because missile has a fixed time-to-live. (range should be a bit less than [netforcelimits] speed x ttl)
    
    fc_requires_los                true
    fc_requires_inrange            true
    fc_requires_enemycolony            false
    fc_manual_target            false
    fc_manual_toggle            false
    fc_manual_launch            false
    fc_controllable                false
    fc_holdsfire                false
    blindfire                 false
    pinpoint                 false

    missile
    {
        tracking         1
        pd             1
        warhead_dam_scale     1 // add standard missile warhead damage modifier
    
        impact_decal        decals/WeaponHit.decal
        impact_decal_width     .5
        impact_decal_height     .5
        impact_decal_depth     .1

        beam_origin        -3
        beam_length        5.5

        impact_sound        Sounds/Weapons/mis_interceptor_pd_impact.wav
        impact_sound_minrange    200
        impact_effect        effects/mis_tarka_dfire_impact.effect
        area_impact_effect     "effects/collide_asteroid.effect"
        Planet_Impact_Effect    "effects/mis_pd_impact.effect"

        thrust_sound        Sounds/Weapons/mis_missile_travel.wav
        
        speed             350
        seek_attenuation     11    // Higher number = tighter turns/faster accel

        ttl            2.5
        model            missile.X
        health            15
        dam            60
        dam_radius        0
        mass            25
        thrust_effect        effects/Missile_small_trail.effect
        thrust_node        EffectNode
        dumbfire_period        0.5

        dam_pop        0
        dam_infra    0
        dam_terra    0
    }

    rating_frate    3
    rating_dam    3
    rating_acc    10
    rating_range    7

    dam_est        60
}

What I need to do is this:

Navigate to a directory with many text based files (they have different extensions),
copy each files data into a spreadsheet where each file is a row and columns are the various code hooks used and the values is what each file specifies for the various hooks.
Each file is different in the hooks used and there is some variation in the format for each hook; integer, string, boolean and space separation in some cases. They all use tab delimitation. Once the editing is done on the spreadsheet I would like to export row by row (all or selected) into the same format.

My approach was this:

Import:
1) create new workbook "import database".
2) text import each text file one by one into a new worksheet using one of jerrys scripts. Then some removal of unwanted artefacts used by engine parser.
3) optimize the worksheets further and transpose for next step.
4) build database one row at a time from each sheet using another script matching up the appropriate columns which are all specified in the target sheet.

Export: (not working but here is the idea).
1) duplicate then populate a template sheet from each row in the database. Template contains formatting needed for .weapon files
2) remove unneeded rows in template.
3) text export (tab delimited) the populated template duplicate then delete, repeat for all rows in database.


I can post my VBA scripts if necessary but I think its easier to see what I am trying to do in my current workbook.:
http://www.mediafire.com/?ircxh5rg68lfcah - My excel workbook to do this
http://www.mediafire.com/download.php?xn96r5bmgnrfcw5 - A sample of 500 .weapon files I would like to import.

Import mostly works but takes 3 steps and is very very slow. And I am stuck with export.



So my question is this: What is an efficient way to do a text file import to populate a spreadsheet with known columns? Using arrays?
Then how do I do the export?

I am still new to VBA but this has all been great learning. I would appreciate any advice on how to do this!