Reformat exported file?

I have a script that creates users from a file received from an external source.
It looks like this
Username;Firstname Lastname;Password

I need to split the lastname from the firstname. It should end up being used in the below statement.
Username;Firstname;Lastname;Password
Ofcourse if you somehow can change the below statement to import directly without changing the source file directly that would be even better.
Users can have a middle name too, and that should be treated as a first name like so:Firstname Middelname; Lastname.

strTextline = objTextFile.Readline
If inStr(strTextline, ";") > 0 Then
arrUsers = split(strTextline, ";")
'Wscript.Echo "Username: " & arrUsers(0)
'Wscript.Echo "Lastname: " & arrUsers(1)
'Wscript.Echo "Firstname: " & arrUsers(2)
'Wscript.Echo "Password: " & arrUsers(3)
LVL 2
mathiesen-dataAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

oBdACommented:
After this, you can use the variables strUserName, strFullName, strFirstName, strLastName, strPassword however you need them:
strTextline = objTextFile.Readline
If inStr(strTextline, ";") > 0 Then
	arrUsers = Split(strTextline, ";")
	strUserName = arrUsers(0)
	strFullName = arrUsers(1)
	strPassword = arrUsers(2)
	arrNameComponents = Split(strFullname, " ")
	strLastName = arrNameComponents(UBound(arrNameComponents))
	Redim Preserve arrNameComponents(UBound(arrNameComponents) - 1)
	strFirstName = Join(arrNameComponents, " ")
	Wscript.Echo "Username:   " & strUserName
	Wscript.Echo "Full name:  " & strFullName
	Wscript.Echo "First name: " & strFirstName
	Wscript.Echo "Last name:  " & strLastName
	Wscript.Echo "Password:   " & strPassword
End If

Open in new window

0
Steve KnightIT ConsultancyCommented:
Try this to split at the " " too... can be made more fool proof. If there are more than one spaces in the name then it take the last word as the surname as the first two words as the first name as it stands

Steve

strTextline = objTextFile.Readline
If inStr(strTextline, ";") > 0 Then
' Split the full name field into first and last names...

   arrUsers = split(strTextline, ";")
   if instr(arrusers(1)," ") > 0 then
      arrName=split(arrUsers(1)," ") 
      if ubound(arrName)>1 then 
         LastName=arrName(ubound(arrName))
         FirstName=arrName(0) + " " + arrName(1)
      else
         LastName=arrName(1)
         FirstName=arrName(0)
   else
      LastName=arrUsers(0)
      FirstName=""
   end if

   Wscript.Echo "Username: " & arrUsers(0)
   Wscript.Echo "Lastname: " & LastName
   Wscript.Echo "Firstname: " & FirstName
   Wscript.Echo "Password: " & arrUsers(2) 
end if

Open in new window


[Edit: was bit slow posting there... similar to oBdA but messier, was quick!]
0
mathiesen-dataAuthor Commented:
Shouldnt these 3 lines:
Wscript.Echo "Lastname: " & LastName
Wscript.Echo "Firstname: " & FirstName
Wscript.Echo "Password: " & arrUsers(2)

Open in new window


look like this to fit my existing script?
Wscript.Echo "Lastname: " & arrUsers(1)
Wscript.Echo "Firstname: " & arrUsers(2)
Wscript.Echo "Password: " & arrUsers(3)

Open in new window

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

mathiesen-dataAuthor Commented:
I am getting confused here. Does the first post also take into account several names in first name?

The rest of my script uses arrUsers(0) to arrUsers(2)
0
oBdACommented:
Yep. It reads the line and breaks it down into variables that re named after their use.
Output with strTextline = "Username;Firstname MiddleName Lastname;Password":
Username:   Username
Full name:  Firstname MiddleName Lastname
First name: Firstname MiddleName
Last name:  Lastname
Password:   Password

Open in new window

0
Steve KnightIT ConsultancyCommented:
When you were referring to my option, the entry

Wscript.Echo "Lastname: " & LastName
Wscript.Echo "Firstname: " & FirstName
Wscript.Echo "Password: " & arrUsers(2)

Open in new window


is correct.  Mine just splits the name  field - arrUsers(1) - into Firstname and LastName. So:

arrUsers(0) = first field
arrUsers(1) = second field - split into First Name and LastName
arrUsers(2) = third field

The split command splits the line up at, in this case, the ; into different array entries
I then split this up using spaces so you can use.

The other one is neater though :-)

Steve
0
mathiesen-dataAuthor Commented:
oBdA, can I just put the code above into the rest of the script and still refer to the
arrUsers(0) - (3) in the rest of my original script?
0
oBdACommented:
Currently no. It could certainly be changed by redefining the arrUsers array accordingly, but ...
1. It makes the script a lot less error prone and easier to read when you use, for example, "strFirstName" instead of "arrUsers(2)" (which is why I used strFullName = arrUsers(1) ... arrNameComponents = Split(strFullname, " ")) instead of arrNameComponents = Split(arrUsers(1), " "))
2. If you ever need to change the input format again, all you need to do is change the few lines that define the "readable" variables, instead of having to search and replace arrUsers(x) all over the place.

In other words: the best solution would be to do a search and replace like this (except obviously for lines 4-6 in my above script), based on your format "Username;Firstname;Lastname;Password":
arrUsers(0) --> strUserName
arrUsers(1) --> strFirstName
arrUsers(2) --> strLastName
arrUsers(3) --> strPassword
0
mathiesen-dataAuthor Commented:
I tried editing my script, but now I get an error.
Line: 87
Char: 1
Error : Expected Statement
Code: 800A0400
Oprettelse-af-Brugere-ArrUsers-edit.vbs
0
oBdACommented:
From what I can see (without being able to test it), all you need to do is remove line 32 ("End If"), and adjust the indentation accordingly. Line 32 closes "If inStr(strTextline, ";") > 0 Then" from line 18, but it should continue until the "End If" line 87.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mathiesen-dataAuthor Commented:
Perfect, it worked.
I attached the final script. It didnt create the user folders, so I had to add some more scripting for that to happen.
KGDC01 is the domain controller.
Below the extra stuff I did:

Set wshShell = CreateObject("Wscript.Shell")
'************************************************
'* CREATE USER DIRECTORY                        *
'************************************************

 strShareName = strUserName
 Set objFolder = objFSO.CreateFolder("\\kgdc01\Users\" & strShareName)

'************************************************
'* Set Permissions on USER DIRECTORY            *
'************************************************

return = wshShell.Run ("icacls \\kgdc01\Users\" & strShareName & " " & "/grant " & strUserName & ":(OI)(CI)F")

Open in new window

Createusers.vbs
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.