Foxpro question

[markw714]

Hi,

I have a series of .dbf tables containing outlook messages in general fields. I have been banging my head against a wall trying to write a foxpro program to export these .msg files into a folder with no success. I have no experience with autoit, but at this point I'm willing to learn to accomplish this task. These files can be pulled out 1 at a time by clicking each general field, clicking the .msg file within and using save-as. Problem is there are over 40,000 messages and that would take a very long time.

Would it be possible to write an autoit script to open a series of .dbf files in foxpro 1 at a time, open a general field in each line, save the .msg file one at a time to an export folder and title each file with a corresponding 6-digit number on each line in the table?

Running VFP5.

[Danp2]

Have you looked at this article? You may be able to use the same technique to save the contents of the General field to the .msg files.

[markw714]

Yes. I've been through all of microsoft and foxpro forums. The problem is the general fields store OLE data and are not made to be extracted like this. Bad storage method.

I just want to find out if it's even a possibility with autoit. I believe I have an ODBC driver.

Edited December 9, 2013 by markw714

[Danp2]

You should be able to sent keystrokes to VFP. You may have issues accessing VFP controls since the controls being used are non-standard.

[markw714]

Most of it should be possible with arrows and enter, but I'm not sure about actually saving the file once the field is open since it displays as an icon. The icon is always located in the same place once the file is open. If I create an excel table of destination file names, can the program cycle through the list and name them line by line?

Edited December 9, 2013 by markw714

[Danp2]

Once you have issued the MODIFY GENERAL command, it would appear that the following would work:

• Press Ctrl-C to copy the contents of the General field
• Switch to an Explorer window
• Press Ctrl-V to paste a copy of the contents and create a new .msg file

[markw714]

 

Once you have issued the MODIFY GENERAL command, it would appear that the following would work:

• Press Ctrl-C to copy the contents of the General field
• Switch to an Explorer window
• Press Ctrl-V to paste a copy of the contents and create a new .msg file

 

 

Maybe, but there are 2 potential issues.

1, once the general field is opened (can literally double click the field, don't need to enter any commands), it displays an outlook msg icon in the top left of the screen. To access the file I need to double click the icon. (not sure about ctrl+c)

2, I need to name the file based on a 6 digit number on the same row as the general field. If I save every .msg file with their actual titles, I will have no way to connect them to their client number. Possible workaround might be naming them based on a column in a workbook.

Edited December 9, 2013 by markw714

[Danp2]

1. Try using the Copy and Paste keyboard commands without double clicking on the icon. It worked in my tests.

2. That does complicate matters. You could possibly rename the file after it is created by the Paste operation.

[markw714]

ctrl + c does work. How would I go about doing the rename?

[Danp2]

One way would be to paste the file into a subdirectory that is empty, then rename the resulting file. After that, you could move the file to a final destination directory.

I believe that I've found a way to accomplish this in pure VFP, although the resulting files have extra data embedded at the end. Let me know if you are interested and I can post some demonstration code.

[markw714]

If you have a code that can get these out, I would be very, very happy. Here is what I tried first. notemail.dbf is the table, the "notemail" column contains the gen fields and the "mailpoint" column contains the 6 digit number I want for the title. (i.e., 123456.msg).

Set Library To ( Home()+'foxtools.fll' )

loForm = CREATEOBJECT("Form")

use C:Usersmedesktopnotemail.dbf IN 0 ALIAS OutlookData

loForm.AddObject("olmailitem", "oleBoundControl")
loForm.olmailitem.AutoSize = .T.

loForm.olmailitem.ControlSource = "OutlookData.notemail"

lcFolder = GETDIR("X:export2","PATH")

IF EMPTY(lcFolder)
RETURN
ENDIF
lcFolder = lcFolder + IIF(RIGHT(lcFolder,1) == "", "","")

SCAN

lcFileName = lcfolder + (forceext(padl(outlookdata.mailpoint,6), "msg"))

loform.olmailitem.saveas(lcfilename)

ENDSCAN

RELEASE loForm

USE IN (SELECT("OutlookData"))

RETURN

 

 

This code runs but doesn't extract anything. If I add a suspend after the first lcfilename=, debugger stops on loform.olmailitem.saveas(lcfilename). I tried switching olmailitem with other ole outlook phrases, same results.

After beating my head against the wall, I changed the gen fields to memo with a hex editor and ran this code:

 

 

set library to ( home()+'foxtools.fll' )
local lcFileName
use C:UsersmeDesktopxNOTEMAIL.DBF
scan
lcFileName = forcepath( forceext(padl(notemail.mailpoint,6), 'msg'), 'X:export2')
copy memo notemail.notemail to ( m.lcFileName )
endscan

 

Everything exported properly but the files were bad. They contained an extra string before the file signature:

* " ŒRþVisual FoxPro Package Package eè JOHNDOE.msg c:softwareclientworknotesDOE~1.MSG ! c:softwareclientworknotesDOE~1.MSG è ÐÏࡱá

instead of just

ÐÏࡱá

Side-by-side comparison of a good .msg with the corresponding bad msg with hex editor shows the file structure is wayyyyyyyy off.

Keep in mind I'm on VFP5  so I can't use several commands (like FILETOSTR()).

Edited December 9, 2013 by markw714

[Danp2]

Keep in mind I'm on VFP5  so I can't use several commands (like FILETOSTR()).

 

Yeah... I was just looking into that. The code that I wrote does use FILETOSTR() and STRTOFILE(), but you should be able to do something similar with fread() and fwrite().

copy memo notemail.notemail to ( m.lcFileName )

 

This gives me an error in VFP9 stating that the field must be a memo field.

Side-by-side comparison of a good .msg with the corresponding bad msg with hex editor shows the file structure is wayyyyyyyy off.

 

Correct. You have to strip off the "extra" data. Here's the code as I have written it for VFP9:

*-- File marker for .msg file
lcHdr = CHR(208) + CHR(207) + CHR(17) + CHR(224) + + CHR(161) + CHR(177) + CHR(26) + CHR(225) + CHR(0)

*-- Open table
USE Notemail

SCAN
    *-- Copy General field to temp table
    COPY NEXT 1 TO Temp FIELDS Notemail
    
    *-- Read contents of FPT file
    lcMsg = FILETOSTR('Temp.fpt')
    
    *-- Locate file marker in string
    lnPos = AT(lcHdr, lcMsg)
    
    IF lnPos = 0
        LOOP
    ENDIF
    
    *-- Determine name of destination file
    lcFile = 'calc_name_here.msg'
    
    *-- Write file to disk
    STRTOFILE(SUBSTR(lcMsg , lnPos), lcFile)
ENDSCAN

*-- Cleanup temp files
DELETE Temp.dbf
DELETE Temp.fpt

HTH, Dan

Edited December 10, 2013 by Danp2

[markw714]

Yeah... I was just looking into that. The code that I wrote does use FILETOSTR() and STRTOFILE(), but you should be able to do something similar with fread() and fwrite().

 

This gives me an error in VFP9 stating that the field must be a memo field.

 

Correct. You have to strip off the "extra" data. Here's the code as I have written it for VFP9:

*-- File marker for .msg file
lcHdr = CHR(208) + CHR(207) + CHR(17) + CHR(224) + + CHR(161) + CHR(177) + CHR(26) + CHR(225) + CHR(0)

*-- Open table
USE Notemail

SCAN
    *-- Copy General field to temp table
    COPY NEXT 1 TO Temp FIELDS Notemail
    
    *-- Read contents of FPT file
    lcMsg = FILETOSTR('Temp.fpt')
    
    *-- Locate file marker in string
    lnPos = AT(lcHdr, lcMsg)
    
    IF lnPos = 0
        LOOP
    ENDIF
    
    *-- Determine name of destination file
    lcFile = 'calc_name_here.msg'
    
    *-- Write file to disk
    STRTOFILE(SUBSTR(lcMsg , lnPos), lcFile)
ENDSCAN

*-- Cleanup temp files
DELETE Temp.dbf
DELETE Temp.fpt

HTH, Dan

 

I used the copy memo command after I converted the gen fields to memo with hex editor. I'm going to see what I can do with this. Thanks.

[Danp2]

OIC. I missed that earlier when I skimmed through your post. ;-)

[markw714]

Yeah... I was just looking into that. The code that I wrote does use FILETOSTR() and STRTOFILE(), but you should be able to do something similar with fread() and fwrite().

 

This gives me an error in VFP9 stating that the field must be a memo field.

 

Correct. You have to strip off the "extra" data. Here's the code as I have written it for VFP9:

*-- File marker for .msg file
lcHdr = CHR(208) + CHR(207) + CHR(17) + CHR(224) + + CHR(161) + CHR(177) + CHR(26) + CHR(225) + CHR(0)

*-- Open table
USE Notemail

SCAN
    *-- Copy General field to temp table
    COPY NEXT 1 TO Temp FIELDS Notemail
    
    *-- Read contents of FPT file
    lcMsg = FILETOSTR('Temp.fpt')
    
    *-- Locate file marker in string
    lnPos = AT(lcHdr, lcMsg)
    
    IF lnPos = 0
        LOOP
    ENDIF
    
    *-- Determine name of destination file
    lcFile = 'calc_name_here.msg'
    
    *-- Write file to disk
    STRTOFILE(SUBSTR(lcMsg , lnPos), lcFile)
ENDSCAN

*-- Cleanup temp files
DELETE Temp.dbf
DELETE Temp.fpt

HTH, Dan

 

Hey,

If it's not too much trouble, could you give me examples of fread/fwrite in that code? I really have very little experience with VFP and of course the MSDN help page isn't helpful

[Danp2]

Found your thread on Foxite. ;-)

Here's a thought... 

• Take the DBF that you modified to change the field type to Memo
• Make a backup copy!
• REPLACE ALL Notemail WITH SUBSTR(Notemail, AT(lcHdr, Notemail))
• Use the COPY MEMO command to save the contents to a file with desired name

Let me know if this works for you. Remember that lcHdr was defined in the code posted previously.

[markw714]

I'm going to give that a go.

Can I use substr with vfp5?

[markw714]

Found your thread on Foxite. ;-)

Here's a thought... 

• Take the DBF that you modified to change the field type to Memo
• Make a backup copy!
• REPLACE ALL Notemail WITH SUBSTR(Notemail, AT(lcHdr, Notemail))
• Use the COPY MEMO command to save the contents to a file with desired name

Let me know if this works for you. Remember that lcHdr was defined in the code posted previously.

 

It worked. You rock. Thank you so much.

[Danp2]

YW. Glad we were able to find a solution.

[markw714]

YW. Glad we were able to find a solution.

 

Me too. Funny how 2 weeks on foxite and nothing, 1 day on a completely unrelated forum and presto!

thanks again