?
Solved

Need help in creating a parsing program

Posted on 2012-08-28
11
Medium Priority
?
390 Views
Last Modified: 2012-08-31
I need to parse the following field from DN into the following table field names CN, OU1, OU2, SDO

CN = jENNFARR
OU1= 0001
OU2 = 730
SDO = 0


dn
cn=JENNRFARR,ou=0001,ou=730,o=SDC
cn=LIARTH,ou=0004,ou=388,o=SDC
cn=TRACEYPACE,ou=1900,ou=680,o=SDC
cn=MAIAMOROE,ou=0006,ou=325,o=SDC
cn=STEENGNE,ou=0016,ou=693,o=SDC


I need a way to parse this information.  Can someone assist thank you
0
Comment
Question by:frank_guess
  • 5
  • 5
11 Comments
 
LVL 13

Expert Comment

by:Ryan
ID: 38341811
Public Sub SeperateAndInsert(str As String)
'Dim str As String
'str = "cn=JENNRFARR,ou=0001,ou=730,o=SDC"

Dim vals() As String

vals = Split(str, ",")
Dim cn As String
Dim ou1 As String
Dim ou2 As String
Dim o As String

cn = Mid(vals(0), 3)
ou1 = Mid(vals(1), 3)
ou2 = Mid(vals(2), 3)
o = Mid(Val(3), 3)

'Create your recordset to insert these values

End Sub
0
 

Author Comment

by:frank_guess
ID: 38342472
I am missing something here.  I need to do this in a update query.
0
 
LVL 13

Expert Comment

by:Ryan
ID: 38342523
Yes, In a different function do a select query.
Iterate every record, calling this function.
Add the update query to the end of the provided function to do the updates.

To do this in a single update query is either going to be a long set of instr() and mid() functions, or write 4 functions to parse each value seperately and call that from a function, which will be slower than using an update routine.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:frank_guess
ID: 38342689
Sorry.  I just got completely lost with your explanation.  Please try again for this old man.
0
 
LVL 13

Expert Comment

by:Ryan
ID: 38342793
Create a function that does your select query

public sub UpdateMyTable()
  dim rs as new adodb.recordset
...
rs.open("SELECT * FROM ..."...)

do until rs.eof
  SeperateAndInsert(rs!dn)
rs.movenext
loop
end sub

also add your update recordset to the end of the function I wrote previously.
0
 
LVL 6

Expert Comment

by:mo_patel
ID: 38343242
or a really simple way would be to copy it into excel into one field as text

go to data > text to columns,

select filed delimiter as other and enter '=' and thats it ,

finally u do text to columns and split 730,o by comma

very simple take 5min
0
 
LVL 13

Expert Comment

by:Ryan
ID: 38343263
yes, if its a 1 time thing, that would certainly be the easiest way.

You can copy the excel results right back to an access table as long as the columns line up.
0
 

Author Comment

by:frank_guess
ID: 38346675
Thank you but I need this to be done on a monthly basis.
MrBullwinkle not sure what you are talking about.  Is this built as part of a button or is this code that needs to be in a module?

Create a function that does your select query

public sub UpdateMyTable()
  dim rs as new adodb.recordset
...
rs.open("SELECT * FROM ..."...)

do until rs.eof
  SeperateAndInsert(rs!dn)
rs.movenext
loop
end sub

also add your update recordset to the end of the function I wrote previously.
0
 
LVL 13

Accepted Solution

by:
Ryan earned 2000 total points
ID: 38346713
Yes, create a button that calls the code.

The code I've given you is incomplete though, I don't have the info, and I thought you could fill in the blanks.
0
 

Author Comment

by:frank_guess
ID: 38347749
Thanks, that gives me a little more to work from.
0
 

Author Closing Comment

by:frank_guess
ID: 38354723
Thanks.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question