Table structure

[darag2358]

I'm trying to import a messy text file in to a table so that I can run queries. How should I cleanup and organize my table? The information is a data security report that shows what kind of mainframe access each area in the company can have. When I import the file, it comes in as one column. I can clean it up enough so that the errant data is removed and the remaining data is listed basically like this:

Screen ABC
Unit(1234) Allow
Unit(4321) Allow
Unit(2314) Allow

Screen DEF
Unit(5612) Allow
Unit(1234) Allow

Screen XYZ
Unit(3321) Allow

And so on....each screen is separated by a blank row. There are some other intracies involved, but that's pretty much how it is laid out. How can I make this in to usable tables? Any ideas are very much appreciated. Thanks in advance.

[Fornatian]

What relationshio exist between the data?
Is there a one-many relationship between the Screen and Unit?
It is worth posting a batch of sample data to play with.

[darag2358]

One unit will have access to multiple screens, so a many to one relationship exists. Here's a sample of actual data. As you can see, HOIO*0269 is listed multiple places. The entire file is about 30,000 lines.

The "Key" is the screen name on the mainframe.

Field1
$KEY(TG03) TYPE(CKP)
UID(HOIO*0254) ALLOW DATA(6/27/91-JKP)
UID(HOIO*0269) ALLOW DATA(6/17/91-JKP)
UID(HOIO*2114) ALLOW DATA(ISRG 2/6/96-JKP)
UID(HOIO*2115) ALLOW DATA(ISRG 4/25/00-JKP)

$KEY(TG05) TYPE(CKP)
UID(HOIO*0254) ALLOW DATA(10/11/99-DA)
UID(HOIO*0269) ALLOW DATA(6/17/91-JKP)
UID(HOIO*0272) ALLOW DATA(10/11/99-DA)
UID(HOIO*2114) ALLOW DATA(ISRG 2/6/96-JKP)
UID(HOIO*2115) ALLOW DATA(ISRG 4/25/00-JKP)

$KEY(TG06) TYPE(CKP)
UID(HOIO*STF0254) ALLOW DATA(6/27/91-JKP)
UID(HOIO*0269) ALLOW DATA(6/17/91-JKP)
UID(HOIO*2114) ALLOW DATA(ISRG 2/6/96-JKP)
UID(HOIO*2115) ALLOW DATA(ISRG 4/25/00-JKP)

$KEY(TG07) TYPE(CKP)
UID(HOIO*STF0254) ALLOW DATA(6/27/91-JKP)
UID(HOIO*0269) ALLOW DATA(6/17/91-JKP)
UID(HOIO*2114) ALLOW DATA(ISRG 2/6/96-JKP)
UID(HOIO*2115) ALLOW DATA(ISRG 4/25/00-JKP)

$KEY(TG08) TYPE(CKP)
UID(HOIO*1798) ALLOW DATA(ISRG 6/1/95-JKP)
UID(HOIO*0254) ALLOW DATA(6/27/91-JKP)
UID(HOIO*0269) ALLOW DATA(6/17/91-JKP)
UID(HOIO*0272) ALLOW DATA(04/23/93-DMA)
UID(HOIO*0533) ALLOW
UID(HOIO*1110) ALLOW DATA(D.GMYREK 11/15/02-RBE)
UID(HOIO*2114) ALLOW DATA(ISRG 2/6/96-JKP)
UID(HOIO*2115) ALLOW DATA(ISRG 4/25/00-JKP)
UID(HOIO*2583) ALLOW

$KEY(TH**) TYPE(CKP)
UID(HOIO*0254) ALLOW DATA(11/19/90-JKF)
UID(HOIO*0257) ALLOW DATA(04/23/97-PJ)
UID(HOIO*0261) ALLOW DATA(5/23/90 DMA)
UID(HOIO*0269) ALLOW DATA(3/2/90-JAP)
UID(HOIO*0272) ALLOW DATA(04/23/93-DMA)
UID(HOIO*0297) ALLOW DATA(3/2/90-JAP)
UID(HOIO*0378) ALLOW DATA(1/25/94-JKP)
UID(HOIO*0533) ALLOW DATA(3/2/90-JAP)
UID(HOIO*1109) ALLOW DATA(P. MYSORE 07/09/99-LWK)
UID(HOIO*1110) ALLOW DATA(ISRG 6/1/95-JKP)
UID(HOIO*1798) ALLOW DATA(ISRG 6/1/95-JKP)
UID(HOIO*2114) ALLOW DATA(ISRG 2/6/96-JKP)
UID(HOIO*2115) ALLOW DATA(ISRG 4/25/00-JKP)
UID(HOIO*2583) ALLOW DATA(3/2/90-JAP)

$KEY(TH13) TYPE(CKP)
UID(HOIO*0269) ALLOW DATA(4/16/92-DMA)
UID(HOIO*0254) ALLOW DATA(4/16/92-DMA)

[Fornatian]

Don't shoot me down too quick but I prefer excel for this type of task, maybe because I find it easier to manipulate and test than access.

Please see the attached excel sample zip. Look at the code then run the macro.

Hope that's what you were after or thereabouts.

[darag2358]

Thanks, Fornation, but I have over 80,000 lines which exceeds Excel's max.

On second thought, maybe I could just split this in half and then try what you have. I'll let you know on Monday.

I too like Excel better for manipulating data and getting it in the order I need. However I'll still probably need to import this back in to Access to do some querying. How should this be organized?

Thanks for taking a look.

[Fornatian]

Getting it into Access is easy once it's in excel, you can link a table to it, import it, use a linked table and frequent append or make-table queries to take a copy of the info etc... The options are abound.

You could even resave it as a csv if you desired and import it that way(if it pleases you)

[darag2358]

Thanks again. I modified your Excel macro slightly to suit my needs and was able to get this in to a usable table. Appreciate the help.

Dan

[The_Doc_Man]

Although it is ugly, you could handle this pretty easily using some VBA code that runs from a general module. You would have to make it a public function if you wanted to run it from a macro. The function could, of course, just return a blanket Boolean TRUE if you didn't care about much else.

The approach I would take is as follows. Help topics you will need are in italics.

1. Write some VBA code to Open the text file. (So make the text file have a predictable name, maybe by renaming it based on your choice for the file name.) Open a recordset (.OpenRecordset method) to some handy table that contains what you need to keep for this operation.

2. In a loop that is protected by an On Error Then .... clause, start reading lines with InputLine

3. Using the InStr function, locate features in the line.

For instance, keyword "$KEY" is usable for an InStr search to identify a new record. So you would identify a new key, find the stuff between the left and right parentheses, and use the Mid$ function to extract that. Don't forget to allow for the parentheses occupying space in this substring.

4. If it is not a $KEY line then it is either a blank or a UID line. The blanks you can just flat skip. Use Len to identify that the line you just input was nil.

5. For the UID lines, it is hard for me to know if the ALLOW clause argument is meaningful because it doesn't look like a valid table name or data element name. In any case you can again extract the parenthetical material by using InStr to locate the left and right parentheses in order to pick out the pieces-parts. Note that InStr has a "start from here" argument, which is how you would find the second set of parentheses if you need that information.

6. Every time you get an UID ... ALLOW ... line parsed out, do a .AddNew method to the recordset you opened earlier. Populate its fields with the key, uid, and allow arguments. Do a .Update method on the recordset.

7. When your "On Error Then..." trap trips, close the file (Close statement) and the recordset (Close method). Evaluate the table afterwards to see if you got the number of records you expected to see. From there, you can use queries to take it farther in terms of reports, counts, etc.

You might have to run this code a couple of times if you have some abominations in the table. For instance one of your UID lines has an allow without any parentheses. So if you HAD to have something there, you would have to either flag the line or toss in a default value.

I would not have suggested this for truly free-form input, but your input is decidedly NOT free-form. It has enough syntactic and semantic markers to allow a simplified approach.

[darag2358]

Fornation's response was very helpful and is what I used to get my table set up.

Doc_Man, your information is laid out perfectly for me to understand. I plan to use this information so that I can more easily automate this. This text file changes sometimes daily so it would be much better if I could keep this within a single application.

I appreciate both of your responses and time. I currently have my table set up and have been running numerous queries on it which has made my life a lot easier!