Parse Text File into SQL Queries VBA

Question

I am attempting to automate some work we are doing. We work on fire alarm systems, and prior to testing we have to demonstrate what will happen in the program prior to testing. So if we hit this smoke detector this will be the result.

I am a relatively new coder.

The panel programming is in a text file that has consistent delimiters, but there are a couple items that are giving me some trouble.

[OC13_ALARM_STATUS_LED]

ALARM '*OC13*':
        FAST    '*_STATLED5';

[OC13_EWSD_WARNING_STATUS_LED]

ALARM '*OC13*EWSDL*':
        FAST     '*_STATLED6',
        LEDOFF   '*_STATLED5';

[OC13_TROUBLE_STATUS_LED]

TROUBLE '*OC13*':
        FAST     '*_STATLED7';



{***DISABLE BUTTONS***}

[0138_DIS_ACT]
SWITCH  'OC4_0138_SW1_DIS_ACT':
    FAST    'OC4_0138_LED129_DIS_ACT';

[DIS_BELL]
MONITOR 'AND_0138_DIS_BELL':
    DISABLE AUD '*_OC12_BELL*',
    STEADY      'OC4_0138_LED129_DIS_ACT',
    STEADY      'OC4_0138_LED131_DIS_BELL';

[DIS_OC12_ELEV]
MONITOR 'AND_0138_DIS_OC12_ELEV':
    DISABLE NSO '*_OC12_RLY_*_ELEV*',
    DISABLE NSO '*_OC12_REDCAP_*_ELEV*',
    STEADY      'OC4_0138_LED129_DIS_ACT',
    STEADY      'OC4_0138_LED133_DIS_OC12_ELEV';

The brackets are the name of the rule, the items on the left of the colon are the inputs, the right side is the outputs. We have a table that has all of the devices. What I want to do is parse this text file making a table with the rules. Then I intend to make a select query using this data to show when this input is activated, this rule runs, and these outputs will activate.

The curly braces are notes that are randomly inserted throughout the file as needed, which is what is throwing me off. It makes the data somewhat non standard. Also there being some with multiple outputs and others with only one.

Thanks Stack.

So what exactly is your question? Are you expecting someone to write all the code for you? If so, you are in the wrong place. — ekhumoro
– ekhumoro, Commented Mar 10, 2018 at 19:18
Fair point, I can get this to import how I want if I remove all of the randomly placed curly braced text. Sometimes the curly braces are inside the rule, sometimes they are above or below. I was mostly looking for some input or guidance. I've found a number of articles dealing with standardized data, but not one I could apply to nonstandard data. — Robert Sullivan
– Robert Sullivan, Commented Mar 10, 2018 at 19:36
I'd like to keep them ideally, but omitting them is a viable option — Robert Sullivan
– Robert Sullivan, Commented Mar 10, 2018 at 19:52

zyd · Accepted Answer · 2018-03-10 20:25:14Z

0

Triple edit: this regex expression seems to work on your example above: re.search('({.*}*)', lines). But you should triple check.

Use a regex to find all the sets of curly braces and replace them with empty strings.

1) read in your file

2) for each line in lines

2) re.sub('{regex}', '', line)

You should be able to grab the comments first using re.search('({regex})', line)

See related: Remove Sub String by using Python

Regex documentation: https://docs.python.org/2/library/re.html

Regex tester: https://regex101.com/ You can copy and paste your text in here and run different regex against it until you find one that matches. You can even have it generate python code from there that will do your search for you. See the code generator tab on the left.

edited Mar 10, 2018 at 20:25

answered Mar 10, 2018 at 19:58

zyd

9351 gold badge8 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Robert Sullivan Over a year ago

Thanks Zyd, I think this'll get me where I need to be.

zyd Over a year ago

The code generator thing is pretty cool, try putting that regex at the top of my post in the search and your example text in the text box, hit the code generator button and copy the output into a .py file. I hadn't seen it before and just tried it out, it should get you going with some example code.

Robert Sullivan · Accepted Answer · 2018-03-13 13:09:33Z

0

It appears they added some features to excel

https://msdn.microsoft.com/library/1ed840b1-7e20-4419-ad2f-d82054c9b2ab

They have power queries that you can use to build a data parser similar to the Regex framework. It's pretty easy and there is a good level of detail on the documentation on how to use it.

When you import the text file hit advanced and it will bring you to the query editor.

answered Mar 13, 2018 at 13:09

Robert Sullivan

439 bronze badges

Collectives™ on Stack Overflow

Parse Text File into SQL Queries VBA

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related