Blank rows in import

[hejmathew]

I have a file which lists grantees, their privileges and whether or not they are admin. This is proving difficult to import due to each grantee having a different number of privileges which revert to the row underneath (see below). I have replaced the actual usernames with 'user 1' etc...

GRANTEE PRIVILEGE ADMIN
---------------------------------------- ---------------------------------------- ------
USER 1 CREATE SESSION NO
UNLIMITED TABLESPACE NO

USER 2 CREATE SESSION NO
SELECT ANY DICTIONARY NO
SELECT ANY TABLE NO
UNLIMITED TABLESPACE NO

USER 3 CREATE SESSION NO
SELECT ANY TABLE NO
UNLIMITED TABLESPACE NO

USER 4 CREATE SESSION NO
SELECT ANY TABLE NO
UNLIMITED TABLESPACE NO

I would like the final structure to be something like the below, where the data is separated into columns with the headers GRANTEE, PRIVILEGE and ADMIN. I have been able to replace the separators with pipes, but am then stuck on what to do next as there is no fixed number of privileges per user, therefore counting the pipes is a non-starter. Can anyone suggest a way of importing this please?

[LordNeilLord]

Hi,

Can you attach the output file?

[MarqueeCrew]

How literal are you looking for us to take you? Do you really want an output with blank rows and suppressed user values? Do you want it to be black background and white text?

Using pattern matching and a combination of formulas I see answers appearing.

Last word = admin access yes/no
Grantee is found as "start of field,User space number(s)"
Privilege is a Trimmed version of what is left behind

These can all be accomplished within a single formula tool and then followed with a Select.

Cheers,

Mark

[hejmathew]

Not literal at all. I just need help with getting the data structure correct!

Attached is a cut of the data which demonstrates the path I am trying to take. I have been able to get some structure but, there should be blank space under each of the GRANTEEs until the next GRANTEE. Instead, the PRIVILEGE has cascaded underneath.

The GRANTEEs are not all called USER X, they could be anything, and there could be any number of privileges so the NO under ADMIN does not necessarily mean the end of the group of privileges. However, With the attached there are blank rows between the groups of GRANTEES, for example row 9, 14 etc.

Could this be used somehow?

[LordNeilLord · Accepted solution]

Try this:

[hejmathew]

Perfect, thanks very much for your help!!