Scripting help needed

OK, this seems like it would be so easy, but I'm not having much luck. And
please be gentle, I am no scripter.

I need to take the data from an Excel spreadsheet or a CSV file such as
shown below:
"CN=User,OU=OU,DC=Domain,DC=com",329949
"CN=User2,OU=OU,DC=Domain,DC=com",456790

And export this data into a text file with particular formatting such as
what follows:

dn: CN=User,OU=OU,DC=Domain,DC=com
changetype: modify
replace: employeeID
employeeID: 329949
-
dn: CN=User2,OU=OU,DC=Domain,DC=com
changetype: modify
replace: employeeID
employeeID: 456790
-

How would I approach this? Like I said, it seems easy, but I'm getting
nowhere.

Marcus
Mon Apr 09 13:50:34 CDT 2007

Barb schrieb:
> OK, this seems like it would be so easy, but I'm not having much luck. And
> please be gentle, I am no scripter.
>
> I need to take the data from an Excel spreadsheet or a CSV file such as
> shown below:
> "CN=User,OU=OU,DC=Domain,DC=com",329949
> "CN=User2,OU=OU,DC=Domain,DC=com",456790
>
> And export this data into a text file with particular formatting such as
> what follows:
>
> dn: CN=User,OU=OU,DC=Domain,DC=com
> changetype: modify
> replace: employeeID
> employeeID: 329949
> -
> dn: CN=User2,OU=OU,DC=Domain,DC=com
> changetype: modify
> replace: employeeID
> employeeID: 456790
> -
>
> How would I approach this? Like I said, it seems easy, but I'm getting
> nowhere.


Save the script, the source and the destinationfile in the same folder.
"NewFileName" will be overwritten each time you run the script.

'-----------------------------------------------
Const NewFileName = "NewFile.txt"
Const SourceFileName = "source.txt"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(SourceFileName)
strLines = Split(objFile.ReadAll,vbcrlf)
objFile.Close
Set NewFile = objFSO.CreateTextFile(NewFileName, True)
For Each strLine in strLines
if strLine<>"" Then
Arr = Split(strLine,",")
NewLine = ""
for x = 0 To 3
NewLine = NewLine & Arr(x) & ","
next
NewLine = replace(NewLine,Chr(34),"")
NewLine = "dn: " & Left(NewLine,Len(NewLine)-1) & vbNewLine
NewLine = NewLine & "changetype: modify" & vbNewLine
NewLine = NewLine & "replace: employeeID" & vbNewLine
NewLine = NewLine & "employeeID: " & Arr(4) & vbNewLine
NewLine = NewLine & "-" & vbNewLine
NewFile.WriteLine NewLine
end if
Next
NewFile.Close
'-----------------------------------------------

mr_unreliable
Mon Apr 09 13:55:50 CDT 2007

hi Barb,

Step 1: Read up on the file scripting object (fso). You
can use it to read in your text file, and write out your
desired re-formatted file.

Step 2: Read up on the "Split" function. You can use
that to split up your input lines. I suggest splitting
based on a comma separator. The split function will
give you a "string array" (zero-based). The employeeID
will be the last element of the string array saMyLine(4).

Step 3: Once you have what you want, you can use fso to
write the new file, line by line. You can use the fso
"text stream" writeline method for this.

It's not going to be a very difficult script to write.
If you wait around long enough, some generous person
will probably come along and write it for you.

cheers, jw
____________________________________________________________

You got questions? WE GOT ANSWERS!!! ..(but,
no guarantee the answers will be applicable to the questions)



Barb wrote:
> OK, this seems like it would be so easy, but I'm not having much luck. And
> please be gentle, I am no scripter.
>
> I need to take the data from an Excel spreadsheet or a CSV file such as
> shown below:
> "CN=User,OU=OU,DC=Domain,DC=com",329949
> "CN=User2,OU=OU,DC=Domain,DC=com",456790
>
> And export this data into a text file with particular formatting such as
> what follows:
>
> dn: CN=User,OU=OU,DC=Domain,DC=com
> changetype: modify
> replace: employeeID
> employeeID: 329949
> -
> dn: CN=User2,OU=OU,DC=Domain,DC=com
> changetype: modify
> replace: employeeID
> employeeID: 456790
> -
>
> How would I approach this? Like I said, it seems easy, but I'm getting
> nowhere.

Adam
Mon Apr 09 14:03:22 CDT 2007


"Marcus Schmitt" <Marcus.Schmitt@gmail.com> wrote in message
news:eDrI1fteHHA.4604@TK2MSFTNGP06.phx.gbl...

(snip)

> for x = 0 To 3
> NewLine = NewLine & Arr(x) & ","
> next

This assumes exactly 4 components in the LDAP DN -- if that's what Barb has
then this is fine -- otherwise there will be a cryptic error message. It may
be better to approach this by looking at the ", between the DN and employee
ID, for example:

--- script starts her ---

Dim myLine, ldapDN, empId, strList

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("input.csv", 1)

Do Until objFIle.AtEndOfStream
myLine = objFile.ReadLine
strList = Split(myLine, """,")

ldapDN = Mid(strList(0),2)
empId = strList(1)

WScript.Echo "dn: " & ldapDN
WScript.Echo "changetype: modify"
WScript.Echo "replace: employeeId"
WScript.Echo "employeeID: " & empId
WScript.Echo ""
Loop

Marcus
Mon Apr 09 14:12:34 CDT 2007

Adam schrieb:
>
> "Marcus Schmitt" <Marcus.Schmitt@gmail.com> wrote in message
> news:eDrI1fteHHA.4604@TK2MSFTNGP06.phx.gbl...
>
> (snip)
>
>> for x = 0 To 3
>> NewLine = NewLine & Arr(x) & ","
>> next
>
> This assumes exactly 4 components in the LDAP DN -- if that's what Barb
> has then this is fine -- otherwise there will be a cryptic error
> message. It may be better to approach this by looking at the ", between
> the DN and employee ID, for example:
>
> --- script starts her ---
>
> Dim myLine, ldapDN, empId, strList
>
> Set objFSO = CreateObject("Scripting.FileSystemObject")
> Set objFile = objFSO.OpenTextFile("input.csv", 1)
>
> Do Until objFIle.AtEndOfStream
> myLine = objFile.ReadLine
> strList = Split(myLine, """,")
>
> ldapDN = Mid(strList(0),2)
> empId = strList(1)
>
> WScript.Echo "dn: " & ldapDN
> WScript.Echo "changetype: modify"
> WScript.Echo "replace: employeeId"
> WScript.Echo "employeeID: " & empId
> WScript.Echo ""
> Loop
>


If the LDAP DN is between Chr(34), of course. Right.

Barb
Mon Apr 09 14:20:01 CDT 2007

Thank you all. This is exactly what I needed.

"Marcus Schmitt" wrote:

> Adam schrieb:
> >
> > "Marcus Schmitt" <Marcus.Schmitt@gmail.com> wrote in message
> > news:eDrI1fteHHA.4604@TK2MSFTNGP06.phx.gbl...
> >
> > (snip)
> >
> >> for x = 0 To 3
> >> NewLine = NewLine & Arr(x) & ","
> >> next
> >
> > This assumes exactly 4 components in the LDAP DN -- if that's what Barb
> > has then this is fine -- otherwise there will be a cryptic error
> > message. It may be better to approach this by looking at the ", between
> > the DN and employee ID, for example:
> >
> > --- script starts her ---
> >
> > Dim myLine, ldapDN, empId, strList
> >
> > Set objFSO = CreateObject("Scripting.FileSystemObject")
> > Set objFile = objFSO.OpenTextFile("input.csv", 1)
> >
> > Do Until objFIle.AtEndOfStream
> > myLine = objFile.ReadLine
> > strList = Split(myLine, """,")
> >
> > ldapDN = Mid(strList(0),2)
> > empId = strList(1)
> >
> > WScript.Echo "dn: " & ldapDN
> > WScript.Echo "changetype: modify"
> > WScript.Echo "replace: employeeId"
> > WScript.Echo "employeeID: " & empId
> > WScript.Echo ""
> > Loop
> >
>
>
> If the LDAP DN is between Chr(34), of course. Right.
>

Al
Mon Apr 09 16:04:37 CDT 2007


"mr_unreliable" <kindlyReplyToNewsgroup@notmail.com> wrote in message
news:emCXHiteHHA.3548@TK2MSFTNGP05.phx.gbl...
> hi Barb,
>
> Step 1: Read up on the file scripting object (fso). You
> can use it to read in your text file, and write out your
> desired re-formatted file.
>
> Step 2: Read up on the "Split" function. You can use
> that to split up your input lines. I suggest splitting
> based on a comma separator. The split function will
> give you a "string array" (zero-based). The employeeID
> will be the last element of the string array saMyLine(4).

If the canonical name has a comma in it, its record might look like this

"CN=User \,Bill,OU=OU,DC=Domain,DC=com",329949

No problem, in that the employeeID will still be the last item; it just
might not be at (4).

Personally, I'd move this field in front of the DN, where it will always be
at (0). Then to get the DN I would split the line on double-quotes and pick
element (1).

/Al

> Step 3: Once you have what you want, you can use fso to
> write the new file, line by line. You can use the fso
> "text stream" writeline method for this.
>
> It's not going to be a very difficult script to write.
> If you wait around long enough, some generous person
> will probably come along and write it for you.
>
> cheers, jw
> ____________________________________________________________
>
> You got questions? WE GOT ANSWERS!!! ..(but,
> no guarantee the answers will be applicable to the questions)
>
>
>
> Barb wrote:
>> OK, this seems like it would be so easy, but I'm not having much luck.
>> And please be gentle, I am no scripter.
>>
>> I need to take the data from an Excel spreadsheet or a CSV file such as
>> shown below:
>> "CN=User,OU=OU,DC=Domain,DC=com",329949
>> "CN=User2,OU=OU,DC=Domain,DC=com",456790
>>
>> And export this data into a text file with particular formatting such as
>> what follows:
>>
>> dn: CN=User,OU=OU,DC=Domain,DC=com
>> changetype: modify
>> replace: employeeID
>> employeeID: 329949
>> -
>> dn: CN=User2,OU=OU,DC=Domain,DC=com
>> changetype: modify
>> replace: employeeID
>> employeeID: 456790
>> -
>>
>> How would I approach this? Like I said, it seems easy, but I'm getting
>> nowhere.

mr_unreliable
Tue Apr 10 11:06:30 CDT 2007

Al Dunbar wrote:
> If the canonical name has a comma in it, its record might look like this
>
> "CN=User \,Bill,OU=OU,DC=Domain,DC=com",329949
>
> No problem, in that the employeeID will still be the last item; it just
> might not be at (4).
>
> Personally, I'd move this field in front of the DN, where it will always be
> at (0). Then to get the DN I would split the line on double-quotes and pick
> element (1).
>
> /Al
>

If one has reason for concern about which element becomes
the "last" item, then one can use "UBound" to get there.

sInput = """CN=User2,OU=OU,DC=Domain,DC=com"",456790"

saInput = Split(sInput, ",")

MsgBox(saInput(UBound(saInput)))

cheers, jw
____________________________________________________________

You got questions? WE GOT ANSWERS!!! ..(but,
no guarantee the answers will be applicable to the questions)