Use Text To Get initials and last 4 of id

[smashly]

how to replace the - with a *space* quickly

StringReplace , have a look in the AutoIt help file under string management for an example..

actually the example in the help file shows just what your after in reverse..

Cheers

[GEOSoft]

Ok I just stumbled on a way to convert the information I get into an excel document with the data divided into cells where a space is. Which I think will make it easier because Ill be able to call a specific cell and get its specific data.

another question is there a way to take the "-" symbol like that in a social security number and create it into a space? Because this would allow me to have the numbers like this

123 45 6789 which could then be used in excel to divide the 3 groups into different columns. Then I could use AI to search a specific cell and write its content out.

So I need 2 things:

Directions on how to use AI to print out a cells data

and

how to replace the - with a *space* quickly

I'll keep looking but if anyone has any ideas I'd really appreciate the help

I'm off to bed so Ill continue this tomorrow

I don't have the link handy but search the Example Scripts forum for ExcelUDF

Download the UDF and look at _ExcelReadCell

[herewasplato]

Ok I just stumbled on a way to convert the information I get into an excel document with the data divided into cells where a space is. Which I think will make it easier because Ill be able to call a specific cell and get its specific data...

Yes, you would think so, but you will run into the same problem that I pointed out above... last names with spaces in them.

SmOke_N has already solved this problem for you in the best possible way... now all that we have to do is put it all together for you. We will help you do that.

From what I understand from your posts is that you are given a file with some info in it and you manually convert the info into the format explained above so that you can type the converted info into another application.

We can have AutoIt convert the entire input file at once - the output of which is another file... or AutoIt can convert the input file and type the converted data into the second application for you so that there is little chance of error.

Let's start with an input/output script... I'll post one for you to try a bit later.

[herewasplato]

...Let's start with an input/output script... I'll post one for you to try a bit later.

Save the input info as a text file named input.txt.

Save the script below as convert.au3.

Place both files into the same folder.

Right click on convert.au3 and select Run Script.

Open/read/print output.txt.

$InputArray = StringSplit(FileRead("input.txt"), @CRLF, 1)

$handle = FileOpen("output.txt", 2)

For $i = 1 To $InputArray[0]
    FileWriteLine($handle, _SocialGetFileName($InputArray[$i]))
Next

FileClose($handle)

Func _SocialGetFileName($sString)
    Local $aSocial = StringRegExp($sString, '-(\d{4})', 1)
    If IsArray($aSocial) = 0 Then Return SetError(1, 0, '')
    $sString = StringTrimLeft($sString, StringInStr($sString, $aSocial[0]) + 3)
    Local $aName = StringRegExp($sString, ' ([a-zA-Z])', 3), $nUBound = UBound($aName)
    If IsArray($aName) = 0 Or $nUBound < 2 Then Return SetError(2, 0, '')
    If $nUBound < 3 Then Return StringLower(StringLeft($aName[$nUBound - 1], 1) & StringLeft($aName[0], 1) & $aSocial[0]);If no middle initial
    Return StringLower(StringLeft($aName[$nUBound - 2], 1) & StringLeft($aName[$nUBound - 1], 1) & _
            StringLeft($aName[0], 1) & $aSocial[0])
EndFunc   ;==>_SocialGetFileName

...let me know if some part of this does not work for you.

[herewasplato]

...or AutoIt can convert the input file and type the converted data into the second application for you so that there is little chance of error...

Save the input info as a text file named input.txt.

Save the script below as convert2.au3.

Place both files into the same folder.

Right click on convert2.au3 and select Run Script.

Manually get to the field that you would normally type the converted info into but instead of you typing it in, press Alt and x together and let AutoIt send the info for you.

You can test the script by sending the info to notepad if you wish.

expandcollapse popup

HotKeySet("!x", "_SendIt")
HotKeySet("{ESC}", "Terminate")

Global $var1, $var2 = 0

$InputArray = StringSplit(FileRead("input.txt"), @CRLF, 1)

$handle = FileOpen("output.txt", 2)

For $i = 1 To $InputArray[0]
    $var1 = _SocialGetFileName($InputArray[$i])
    Do
        TrayTip("Press Esc to exit", $InputArray[$i] & @CR & "Press Alt-x to send: " & $var1, 10)
        Sleep(1000)
    Until $var2
    $var2 = 0
Next

FileClose($handle)

Func _SocialGetFileName($sString)
    Local $aSocial = StringRegExp($sString, '-(\d{4})', 1)
    If IsArray($aSocial) = 0 Then Return SetError(1, 0, '')
    $sString = StringTrimLeft($sString, StringInStr($sString, $aSocial[0]) + 3)
    Local $aName = StringRegExp($sString, ' ([a-zA-Z])', 3), $nUBound = UBound($aName)
    If IsArray($aName) = 0 Or $nUBound < 2 Then Return SetError(2, 0, '')
    If $nUBound < 3 Then Return StringLower(StringLeft($aName[$nUBound - 1], 1) & StringLeft($aName[0], 1) & $aSocial[0]);If no middle initial
    Return StringLower(StringLeft($aName[$nUBound - 2], 1) & StringLeft($aName[$nUBound - 1], 1) & _
            StringLeft($aName[0], 1) & $aSocial[0])
EndFunc   ;==>_SocialGetFileName

Func _SendIt()
    $var2 = 1
    Send($var1)
EndFunc   ;==>_SendIt

Func Terminate()
    Exit
EndFunc   ;==>Terminate

[RAMMRODD]

WOW! that first one worked perfectly! I thank you for the 2nd one but I think I can handle that part, its not actually just entering username (FML1234) then a date, then an In-time, then an out-time, then an instructor, then a class. I can handle that its relatively easy but the conversion (which you fixed) was the hardest part.

THANKS!!!

chad

PS

is there any way I could get you to add some comments in explaining what each line does? You dont have to, but I tend to learn from hands-on-work and if I saw what you did to make each thing happened I could learn other ways to use it :-)

PSS

I also tested it with a 4 name input and it still did it right, now is there a way to make the inputs with 4 names different from the others? a simple bold of the text would work. I'd really appreciate you TELLING me how to do it, instead of just handing me the scripts.

Edited May 11, 2007 by RAMMRODD

[herewasplato]

...is there any way I could get you to add some comments in explaining what each line does?...

expandcollapse popup

; fileread(input.txt) will return all of the contents of input.txt
; in this case, the returned info is fed to stringsplit.
; stringsplit turns the data like this:
;  1) 123-45-6789 Bo John Hoe
;  2) 321-54-9876 Van Der Lee-Jones Jo Anne
;  ...
; into data like this:
;  $InputArray[0] = the number of elelments in the array
;  $InputArray[1] = 1) 123-45-6789 Bo John Hoe
;  $InputArray[2] = 2) 321-54-9876 Van Der Lee-Jones Jo Anne
;  $InputArray[3] = ...
$InputArray = StringSplit(FileRead("input.txt"), @CRLF, 1)

; opens (creates, if need be) a file named output.txt
; and opens it in a way that erases any previous data
; fileopen returns the handle for the file
; a handle is a special value that windows assigns
; to a file each time it is opened.... I think
$handle = FileOpen("output.txt", 2)

; loops thru each element in the array
; from 1 to "the number of elelments in the array"
; the first time thru the loop,
; it will work with $InputArray[1] = 1) 123-45-6789 Bo John Hoe
; because the $i is equal to 1 the first time trhu the loop.
; the contents of $InputArray[1] will be fed to the function named
; _SocialGetFileName and it retuns jhb6789 and
; jhb6789 is fed to filewriteline which writes it to the file
; indicated by the handle
; no error checking is performed within the loop, but it could be added
For $i = 1 To $InputArray[0]
    FileWriteLine($handle, _SocialGetFileName($InputArray[$i]))
Next

; close the file indicated by the handle
FileClose($handle)

; SmOke_N will have to explain this magic
Func _SocialGetFileName($sString)
    Local $aSocial = StringRegExp($sString, '-(\d{4})', 1)
    If IsArray($aSocial) = 0 Then Return SetError(1, 0, '')
    $sString = StringTrimLeft($sString, StringInStr($sString, $aSocial[0]) + 3)
    Local $aName = StringRegExp($sString, ' ([a-zA-Z])', 3), $nUBound = UBound($aName)
    If IsArray($aName) = 0 Or $nUBound < 2 Then Return SetError(2, 0, '')
    If $nUBound < 3 Then Return StringLower(StringLeft($aName[$nUBound - 1], 1) & StringLeft($aName[0], 1) & $aSocial[0]);If no middle initial
    Return StringLower(StringLeft($aName[$nUBound - 2], 1) & StringLeft($aName[$nUBound - 1], 1) & _
            StringLeft($aName[0], 1) & $aSocial[0])
EndFunc   ;==>_SocialGetFileName

As for changing the script to highlight certain entries... bold is not an option in a text file, but maybe *** could be added to the end of the lines of interest. SmOke_N would have to make that change to the function he supplied to this thread.

...enjoy...

[SmOke_N]

; SmOke_N will have to explain this

; Return an array at [0] element of the first 4 digits found after a hyphen
; Remove the Social Security string from the original string passed to the function
; Return the first letter of each string found after a space
; If no letter was found (a - z) or only 1 letter was found, return a blan string and set error
; If No middle initial Return the file name with a 2 letter (First/Last/SS) and 4 number social
; Return the (the letters are upper case, so Stringlower them) First + Middle + Last + 4 digit SS#

Func _SocialGetFileName($sString)
    Local $aSocial = StringRegExp($sString, '-(\d{4})', 1)
    If IsArray($aSocial) = 0 Then Return SetError(1, 0, '')
    $sString = StringTrimLeft($sString, StringInStr($sString, $aSocial[0]) + 3)
    Local $aName = StringRegExp($sString, ' ([a-zA-Z])', 3), $nUBound = UBound($aName)
    If IsArray($aName) = 0 Or $nUBound < 2 Then Return SetError(2, 0, '')
    If $nUBound < 3 Then Return StringLower(StringLeft($aName[$nUBound - 1], 1) & StringLeft($aName[0], 1) & $aSocial[0]);If no middle initial
    Return StringLower(StringLeft($aName[$nUBound - 2], 1) & StringLeft($aName[$nUBound - 1], 1) & _
            StringLeft($aName[0], 1) & $aSocial[0])
EndFunc   ;==>_SocialGetFileName

[RAMMRODD]

you guys are amazing!!!!!!!!!! Thanks so much, Now I will attempt to digest this info and put it to other uses. Its my way of learning :-)

Thanks

herewasplato and SmOke_N

Chad

[herewasplato]

You are welcome... it is just a hobby (or an obsession, depending on how you look at it).

-MSP-

[Confuzzled]

Thanks guys for all the time you've spent working with this. The information that I need does origianally come from a database, BUT I have absolutley no access to that database I'm simply given a list of socials and full names in paper or word document form and am told to input them into another database which I have some access to. I'm not sure exactly what it is I know its sent back into access but the .exe we use isnt access. Im not sure how the 2 are connected but I need this to take text no fields or cells and rearrange them in the needed order.

thanks again for the effort put into this

Chad

Dumb question: Can you ask whoever is giving you the data in plain text format to give it to you in comma separated values (CSV) when they do a database report/extract so that the relevant fields are clearly delineated? You could even mention the current mantra "XML" - a methodology that is specifically designed to solve the exact problem you are having - reliable data interchange. You would then only have to do a little string manipulation to extract the last four digits of the SSN and reassemble the order of columns to get reliable results every time.

Solve the problem once, and then think about it no more. Your manual way requires a lot of mental effort for every record you process.

[PsaltyDS]

Dumb question: Can you ask whoever is giving you the data in plain text format to give it to you in comma separated values (CSV) when they do a database report/extract so that the relevant fields are clearly delineated? You could even mention the current mantra "XML" - a methodology that is specifically designed to solve the exact problem you are having - reliable data interchange. You would then only have to do a little string manipulation to extract the last four digits of the SSN and reassemble the order of columns to get reliable results every time.

Solve the problem once, and then think about it no more. Your manual way requires a lot of mental effort for every record you process.

Can a brother get a LOUD AMEN!

[RAMMRODD]

My Bosses barely know how to burn cd's so they'd look at me and ask what plain text format was after I'd got done saying all of that.

In other words, thanks for the advice BUT to much computer talk gets them to where they say screw everything and they wouldnt let me use the simple script that was already written.