Help with word import

[quad7]

Hello All,

I have a number of word documents from which I would like to get data to place into a table in Alpha 5. I have looked at the import operations and have even tried to import the document first into excel and then do an import into alpha five. Unfortunately, the number of documents is quite large...in excess of (4) four thousand!. Doing it 'manually' then is really not an option! I know that this can be done in using xbasic, and I have searched the forums for just such help. But I haven't yet become an xbasic 'expert'! (not for want of trying, though!).

The documents are all laid out in the same way, that is, the data that I want is in the same position on every document and all the documents reside in the same folder on the clients' machine. (See jpg image for an example).

There exists on the document the clients' header information(not shown in the jpg example) and some other footer text which I don't need.

If anyone has an idea on what would be the most efficient way to accomplish this task, (and any code or function which might make it possible!) any help would be greatly appreciated!
Thanks in advance.
exampleDoc.JPG

[WaltMorgan]

Re: Help with word import

Have you considered converting the word files into text files and then parsing them?

[Stan Mathews]

Re: Help with word import

I have looked at the import operations and have even tried to import the document first into excel and then do an import into alpha five. Unfortunately, the number of documents is quite large...in excess of (4) four thousand!.

Use the example in ole.create() to convert the documents to excel. (Wrap a loop around the example feeding the document names.) I don't remember the parameter for wordApp.ActiveDocument.SaveAs(NewDoc,0) to save as excel at the moment but you can discover it in the macro editor.

Then design an import operation, convert it to xbasic. Wrap that script in a loop as above.

[Stan Mathews]

Re: Help with word import

There doesn't seem to be a way to save a Word doc as Excel. You could save them all as txt (parameter is 2) and then parse them as suggested.

[G Gabriel]

Re: Help with word import

You should be able to do this very easily with xbasic, except for one step.
The first step is to save these files as txt. No, you don't have to do that manually. There are many software (I believe some shareware as well) to change a whole batch of files from doc to txt.
Once you do that, from there it's a snap. You could read these files into alpha table (an UDF is probably appropriate here).
Here is a snipet of code to read a txt file into an array (you could modify it to read directly into table, or from the array to a table):

dim data (10) as C
file_pointer = file.open("C:............test.txt", FILE_RW_SHARED)
Data[1] = file_pointer.read(1000)
file_pointer.close()

The reason to read it first into an array is to parse each documents to meaningful data (from, to ...etc) and add each as a field in the table.

[Clunes]

Re: Help with word import

The real problem that I see is that by automating this process, the potential exits for a number (probably a lot) of documents not to properly align and therefor presenting additional reconciliation issues that are hard to track down. Is the data on the document essentially required or you are simply importing to establish the history of trading? If you are trying to simply create a history, then it may simpler to have a button on the client form that opens a list showing the history. Click on the document you may want to view from the list and open it.

[Ted Giles]

Re: Help with word import

If the documents have names, why not just build a file list from which you can fire Word and view them?
The Person Name could be imported from a .TXT document position to create a root record?

Just noticed, that's what Mark sort of just said. Doh!

[quad7]

Re: Help with word import

To everyone who responded... A very big THANK YOU!
I struggled with and tried your suggestions and was quite amazed at the ease with which I was able to convert Word docs in txt files and even with the parsing of the data!
Then I saw Mark Pearson's suggestion that I forget about automating this process and just put a button on the form
for the client to view the historical data.

Now I am stuck ( with the code that is). I realize that
1. I want to open a word document based on the "customer Name" on my form "customers".
2. There may be more than one word document with the customer's name in the folder- so i need to present a 'filtered' list for the user?
3. The file name for the word document contains the "customerName" somewhere within!
3. Close the word application from alpha when the user is finished viewing the historical data

I have looked at filefind.grep(). contents() but I am unsure as to the usage.
I feel that there MUST be a really simple way to do this..but I am so caught up experimenting with xbasic that I " can't see the forest for the trees"!
Any help?

[Clunes]

Re: Help with word import

Attached is a script you can import and try. In my case it open tech documents fro a button click on a form. The very first line gets text from a field on a form. It uses this text to show an xdialog with all the documents that contain that text. It then allows you to select the document and open.

Note you will need to edit the lines where it is looking for pdf and change to doc You will slo need to edit lines where it is looking for the path to the documents. In mycase that path has a TDL in it.

[Stan Mathews]

Re: Help with word import

When you were doing your due diligence, you passed over several options to arrive at filefind.grep(). One of those would have been

filefind.get()

as in

list = filefind.get(path + "\*"+customer_name.value+"*", FILE_FIND_NOT_DIRECTORY, "X")

assuming that customer_name is the object name of the "customer Name" field on your "customers" form and substituting/establishing the path variable.

[quad7]

Re: Help with word import

Stan,
The more I browse these forums, and try to create "meaningful" applications, the more I am humbled by those with so much more experience and "know how" than I.
I am ( as I am sure are many others) eternally grateful for the likes of you and Mark and others!

[Stan Mathews]

Re: Help with word import

My "experience" is nothing more than having encountered the same issues you face and making enough errors along the way to finally find the way to make some things work. As I indicated, you did your "due diligence". You found the filefind methods. That's so much more than many would have accomplished.

I salute your efforts.

Reading post #8 a bit further.....

3. Close the word application from alpha when the user is finished viewing the historical data

(Really point 4, but who's counting)

It is likely possible to find the handle/name of the opened word window and close it when Alpha gets focus once more but wouldn't you assume that the user has enough sense to close the document?

[quad7]

Re: Help with word import

Thank you Mark for your quick response.
This part of my App is the only thing that's keeping me from a rollout to the customer and I do appreciate your help!
However, I attached your script to my form after making the changes you suggested.
The dialog box comes up and it is empty...so I assume that my 'path' statement is incorrect.
My word files reside in G:\worddocs.....so should I replace the two instances of your "TDL" with my path?
When I do the list is still empty! when I try that line of code in the interactive, it returns : G:\A5V10MyApplicationG:\worddocs- What am I doing wrong?

[quad7]

Re: Help with word import

Thank Stan for your uplifting comments!
Sorry for the numbering snafu!
I think it's exhaustion! I've been at it all day!
And as for the user having enough sense to close the word document....I assume nothing given the state for the current record keeping system my client currently has in place!
So I just wanted to make the application as "idiot proof" as possible!

[Clunes]

Re: Help with word import

The issue you have is that path pickups up the path to your db first so change

path = a5.get_path() + "\TDL\\" + docpart

to

path = "G:\worddocs\" + docpart or

"G:\worddocs\\" + docpart

or better still to gid rid of the \ issues than

path = "G:" + chr(92) + "worddocs" + chr(92) + docpart