Reading Values from a A file 1

[MyFlight]

I need to Setup a Script File that will Read values from a file (not sure if it will be text or Excel). The file size varies. If text it is Comma Deliminated and I need the 2 field which is enclosed in quotation mark, size varies between 3 and 7 chars). If excel the value would be in the 2nd cell. I need to do this for each line in the fiel (sometime fairly Large files). I need to read this value and send it via ProComm to the Equipment I am connected to. EXAMPLE: File Name C:\Macros\TestFile.txt

Example of TestFile.txt viewed in WordPad: 1,"2000"","","","","","","Smith Jay",.....ETC
1,"15020"","","","","","","Smith Jake",.....ETC
1,"120020"","","","","","","Smith Jeff",.....ETC
1,"2001"","","","","","","Smith John",.....ETC

I need to Copy the 2000 from the first line and Send it Via ProComm to the Equipment I am Connected to (Via Modem). Then perform some other functions, and repeat until the I reach the End-Of-File.

Any suggestions will be greatly appreciated.

 

[knob]

Reading data from the text file would be relatively straightforward. Here is a script that reads testfile.txt and saves the value in a string variable called sValue:

proc main
string sInput, sValue

fopen 0 "c:\testfile.txt" READ TEXT
if success
while not feof 0
fgets 0 sInput
if success
strtok sValue sInput "," 2
strreplace sValue "`"" ""
endif
endwhile
endif
endproc

As for reading from Excel, I don't have anything handy at the moment, but hank might. I can probably bang something together tonight as well (during intermission of the hockey game that is!).

 

[hankm3]

Hello,

Well first, if it's a Flat Text file there is no problem. But you can't have it both ways. If your File is an Excel then you will have to DDE to grab the Fields. I posted a Help File Link for DDE that you will need for Excel.

For a text file you can use the Strtok Function. Example:

proc Getdata
string sTok1
string sTok2
string sLine

fopen 1 "C:Macros\Testfile.txt" READ TEXT
fopen 2 "C:\Macros\TestOut.txt" CREATE TEXT
while not feof 1
fgets 1 sLine
strtok sTok1 sLine "," 1 ;* 1st Field in File
strtok sTok2 sLine "," 1 ;* 2nd Field in File (2000)
fputs 2 sTok2
endwhile
fclose 1
fclose 2
endproc

print TestOut.txt

"2000"
"15020"
"120020"
"2001"

This will extract the "2000" Field from each line in the File and store it into the TestOut.txt file.

Maybe this will get you started. Let me know if you need any additional help

Hank

 

[hankm3]

Hi,

I was looking thru my Scripts and I found 2 that might help you out. One Script Filters a Nortel DMS-100 OMPR and Extracts the Data and Sends it to an Excel Spreadsheet. The Second Script sends Data from a Text File to a Remote Database via a Modem Connection. I can probably combine these two files into a DDE Extractor so it gets the Data from Excel and sends it to the Remote Database.

Give me a day or so and I'll see if I can get you a Working Model put together.

Hank

 

[knob]

Here's the script I've come up with so far (a modified version of the example in the ddeadvise discussion from the ASPECT help file) to read the data from Excel. It works, but the problem is determing when you have read an empty cell, which would theoretically mean you have read all of the data in the spreadsheet and are now just reading the empty cells at the bottom of the spreadsheet. I was expecting an empty cell to be read as a null string, but that does not appear to be the case. Hank might have an idea on where to go from here. One other option I thought of is checking to see if the cell contains a double quote, like the data in the text file does. If so, then you could key on the absence of a double quote to exit the while loop and close the spreadsheet.

string szText ; Text read from DDE link to Excel.
proc main
long LinkVar, SystemVar ; Variables containing DDE Id's.
integer iRow=1
string sCell

; Set up DDE links to Excel's system and a spreadsheet. Excel
; must be running in order for this script to work properly.
if ddeinit SystemVar "excel" "system"
ddeexecute SystemVar "[FULL(TRUE)]" ; Maximize the spreadsheet.
if ddeinit LinkVar "excel" "sheet1" ; Set up link to spreadsheet.
while 1
strfmt sCell "R%dC2" iRow
dderequest LinkVar sCell szText ; Read info from DDEPOKE.
if nullstr szText
exitwhile
endif
usermsg szText ; Display text on status line.
iRow++
endwhile
ddeterminate LinkVar ; Break DDE link to spreadsheet.
ddeterminate SystemVar ; Break DDE link to Excel.
else
errormsg "Couldn't establish DDE link to spreadsheet!"
endif
else
errormsg "Couldn't establish DDE link to Excel!"
endif
endproc

 

[MyFlight]

Knob & Hank,

First off let me say thank you for so rapid a Response to my plea. More Info for Clarification: I am Connecting to a Phonemail system and need to read Extension numbers for a File. The Orginal file is a Text File, and it seems that it will be easier to read than from an Excel file. I need to copy this field and SEND it to the PHONEMAIL System I am connected to (via 56k Modem). I don't know if this is much different than sending it to TestOut.txt file. Basically I am:
1. Downloading Extens from PhoneMail to a Text File.

2. Accessing the PhoneMail system to peform a Change or Modification of Extns. Move to the Modify PhoneMail Box Prompt.

3. In order to modify I need to Enter the Exten Number, and Cannot use the Modify All Statement (i.e. Downloading the File

4. Copy extn from file to PhoneMail, (i.e Edit PM Box), then Step thru PM Box to the Password Prompt and change it to a generic Password(This will be the Same Password for Every PM Box.

5. The password prompt is between the 5th and 9th Prompt depending on the Number of Extensions in the PM Box. Basically I will be hitting Enter Until I see the prompt "PhoneMail Password : (Previous = ##########): ". At this time I would enter the Password.

6. The Password would probably come form a User Input Line (sdlginput), just incase I need to change it.

7. After the Password is entered (i.e. sdlginput and Enter Key. I would send a ; (SemiColon), which brings me to the Enter Extension prompt, I.e. Repeat Process.

Thanks again for the quick and consie replies.

 

[knob]

The transmit command is used to send information from your computer to the remote system, so that should take care of most of the problems you mentioned. For example, in the script that Hank posted, instead of using the fputs command to save the string to a file, you would use transmit sTok2 to send the value of that string to the remote system.

As for the variable number of Enters you would need to press to receive the password prompt, you could tackle this situation in a couple different ways. If the same prompts always appear, you could use pairs of waitfor/transmit commands to skip through those extraneous prompts, then use another waitfor command to know that you have reached the password prompt and can now transmit the password. I'm not certain how well this would work if you have a variable number of prompts though. The other option you have is use a when target command to send an Enter whenever one of the extraneous prompts is received (this would probably rely on the unneeded prompts being identical). The when target command would call a procedure that just sends an Enter and then returns to where the script was previously executing. You would want to use the when clear command just before sending the password so that the when target commands are no longer active elsewhere in your script.

One thing that may make your life a little easier is using Procomm's Script Recorder to get a rough framework that you can build on. The recorder works by matching up text you send in response to data received by your computer and creating matching waitfor/transmit statements. This recorded script can then be played back and the exact same actions (for the most part) will occur. Of course, this recorded script can't be used in everyday circumstances because you won't be modifying the same mailbox each time. However, the recorder can be used to get a basic script that you can then expand upon. To use the recorder, first select the Tools | Scripts | Start Recorder menu item. Next, connect to the PBX (or you could first connect then start the recorder, it just depends on your situation) and issue the commands that you wish to automate. When you are done, select the Tools | Scripts | Stop Recorder menu item. This will create a script that is called record.was by default. If you open this file, you will see that it is full of waitfor and transmit statements. The way to make this script usable for your purposes is to modify the transmit commands to send the value of a string variable, rather than a hard-coded string. This may require only a little modification of the script, or it might be a much bigger process depending on how involved the processes are you are undertaking.

 

[hankm3]

Hello,

This is what I came up with the DDE Request. It will Strip out the " from Field 2. This is based on an Excel 97 File in the Following Format: Note that the First Entry is 1.

1 "2000" "" "" ""
2 "1200" "" "" ""
3 "2400" "" "" ""

The Text File Output will be:

1 2000
2 1200
3 2400

NOTE: You'll need to alter the PathNames:

;* Global Variables *

integer Ro = 1 ;* ROW Number
integer C1 = 1 ;* COL 1
integer C2 = 2 ;* COL 2
string sOne, sTwo
string sStrR = "R"
string sStrC = "C"
integer sField1
integer sField2
string sOutPut

proc Main
long AppLink ; Link to Excel Appl.
long AppChan ; Application Channel to Topic
string szFile = "C:\Files\TestFile.xls"

if not isfile "C:\Files\TestFile.xls"
Errormsg "TestFile.xls NOT FOUND"
exit
endif

;* DDE SESSION FOR Opening File and Reading Data"

if ddeinit AppLink "excel" "system" ;* Open Link to Excel
ddeexecute AppLink "[MAX(TRUE)]" ;* Maximize Excel
ddeexecute AppLink "[OPEN(`"C:\Files\TestFile.xls`&quot]"
ddeexecute AppLink "[FORMULA.REPLACE(`"`,``)]"
if ddeinit AppChan "excel" szFile ;* Channel to Sheet 1
fopen 1 "C:\Files\TestOut.txt" CREATE TEXT
while 1
strfmt sOne "%s%d%s%d" sStrR Ro sStrC C1
strfmt sTwo "%s%d%s%d" sStrR Ro sStrC C2
dderequest AppChan sOne sField1
dderequest AppChan sTwo sField2
if sField1 == 0
usermsg "Exit Point"
exitwhile
endif
strfmt sOutPut "%d %d" sField1 sField2
fputs 1 sOutPut
Ro++ ;* Increment Row Number
endwhile
fclose 1
usermsg "DONE"
ddeexecute AppLink "[File.Close(`"C:\Files\TestFile.xls`&quot]"
ddeunadvise AppChan "R1C1"
ddeterminate AppChan
ddeterminate AppLink
else
errormsg "EXCEL DDE CHANNEL FAILED"
endif
else
errormsg "EXCEL DDE LINK FAILED"
endif
endproc

I'll Play with this some More and will Post what I come up with. Just a Note that you could Auto Launch Excel and then Auto Close Excel.

Hank

 

[hankm3]

Just Figured this Out ! Stupid Me...

If you are working with Number Fields only.. Then the Exitwhile would Look Like this.

if sField1 == 0
if sField2 == 0
usermsg "Exit Point"
exitwhile
endif
endif

This way, you could have Field 1 start with 0 instead of 1.. Also the Max Records for Excel 97 is 65,536.

Hank

 

[hankm3]

Hi Guys,

This may be a False WARNING; but wanted to let you know...

I was working on the DDEREQUEST and it appears that the process of getting the Data FROM Excel is Far More Complicated than I First Thought. Digit Fields seems to be a simple Deal; but the Link and Channel Shutdown CAN CAUSE Damage to The EXCEL Application. I started Playing with STRING Fields and KILLED EXCEL on my Machine.

I will be working on this ISSUE this week, So in the Mean Time; IGNORE the Above CODE... Please...

Sorry for the Inconvience !!

Hank

 

[MyFlight]

Hank3 and Knob,

Since this Script is getting a little large and hard to explain. Would either of you mid if I E-Mail'd what I have to eiter of you with Explanations and Examples?? Please let me know, thanks again for the help.

 

[knob]

You can drop me a line at aspect@aspectscripting.com if you like.

 

[hankm3]

Drop me what you have and I'll take a Look.

hmc4500@bellsouth.net

Hank

 

[icetea]

MyFiight,

Is this script to change phone mail user number?