Need Help Importing Data from .doc files

[lester]

Hello, I have created a simple database for restaurant listings. The fields are Name, Address, Town/City, State, Zip Code, and Phone Number.

My listings are currently in .doc format from MS Word 2000. They are in single line form with the name with a dash(-) then the street address with a comma, then the town/city with a comma, then the state with space then the zip code with a comma, and then the phone number.

Here is an example of what all my listings look like:

Elkhorn Bar & Grille - 38750 Highway 82, Aspen, CO 81611, 970-925-1500

All my listings are categorized by state, so I have 50 different .doc files.

There are over 11,000 listings, so you can see that it would take me years to manually type them in to the database.

I am assuming that I need to create some kind of script that will enable these listings to be properly imported into the fields and that the - after the name of each listing and the commas after the other info in each listing will have to be in the script for everything to be imported into the proper fields.

I would really appreciate any help with this. I tried a while back at the FileMaker Cafe, and the 1 response I received wanted me to pay him to help me.

Thanks in advance for any help with this, a simple step by step guide to creating this script would help me so much!!

[SteveB]

Going directly from Word to Filemaker is a royal pain, and I never had much luck. However, you can easily go from Word to Excel to FM. Excel has more flexibilty in terms of what it can input and output. You'll need a very simple Import command which you shouldn't have any trouble writing...basically match up the fields in the input file with those in your FM database. After it works properly, you can embed it in a script for easier automation.

[lester]

Hi Steve,

Wouldn't it still take forever to import 11,000+ listings from Word to Excel?

Couldn't I just copy and paste the .doc lists to .txt lists and import to FM from the .txt file?

I was just looking around in Excel and don't see any feature for importing from Word. I'm not sure how to do that except for entering everything manually, which would take me just as long as manually typing all my listings into FM.

Thanks

[SteveB]

It will run very rapidly. I imported 7000 records in less than 1 minute. You need to save the Word file as one of the *.txt file options from the Word Save as menu. Your Word structure is prone to problems: what happens if the restaurant name has a dash for example? I would try to clean up as much as possible in Word...try to make it a comma or tab delimited file. The resulting text file will then easily go into Excel.

[lester]

How do I import the listings from Word to Excel? The listings in Word are all in single line form. I confess that I know very little about Excel.

Thanks

[andygaunt]

Hi,

First, as steven pointed out, you really need to create a common seperator between each type of entry.

Try to replace the - where appropriate with a comma if you know you are not going to use a comma with an actual field value.

If you are, substitute the - and space and , that you currently have seperating your data to tabs.

Now, if you then save as a text (*.txt) format each record is seperated by a return.

So, in excel, choose file > open. Then select all file types and choose the .txt file.

This will open a wizard.

Choose delimited format. Click Next. Then, select the seperator (either the comma or tab you chose to use from above). You will see the preview pane below seperate the data into columns. Click next and finish.

Your data is now in excel.

OK. Now, make sure row 1 is the field names. Choose File > Save and save as an excel document.

This can now be dragged straight onto the filemaker app and it will convert it into a filemaker file, or you can import from this file type.

HTH

[The Bridge]

Hi Lester,

As Steveinvegas pointed out, you may run into problems if, for example, a restaurant has a hyphen in its name. However, presuming that this is not the case, you can do a Find/Replace in Word to help you out.

Elkhorn Bar & Grille - 38750 Highway 82, Aspen, CO 81611, 970-925-1500

If this example represents the structure of all of the listings, I would replace all space-hyphen-space with commas, and since each .doc represents a different state, replace the state abbreviation with itself followed by a comma. Then save the file as text-only. Voila, you have a comma-delimited text file which you can import into FileMaker.

I would recommend going through the records in Table View just to make sure everything's where it's supposed to be, but this will still save you a lot of typing.

[lester]

That is a problem though. I can't go through 11,000+ listings and change every - to a comma. That would take forever. That would take me just as long as manually typing all the listings into FM.

I did just try importing using the delinear method. It starts to work, but then gets messed up because I have no comma after the state abbreviations.

I chose - in the "other" category, but it pushes the words together too tight and they get all broken up.

Sigh!

Thanks anyway.

[The Bridge]

Microsoft Word has a great Replace function. It is just below Find in the Edit menu.

In the top box, enter " - " (without the quotation marks).

In the bottom box, enter "," (again, without the quotation marks).

Click on Replace All.

To add a comma to the end of the state abbreviation, in the top box enter "CO" (for example) and in the bottom box enter "CO," (no quotation marks, I just put them here to separate the find/replace text from the rest of my post).

Hope this helps.

[kenneth2k1]

Hey yall!

Yeah, it seems Andy and Pete have layed it all out for you. I've done this before with 2500 records and it works fine. We had to separate out a * character, so what we did (once we got it in excel) was go to Data/Text to Columns, select Delimited then specified the delimiter as the desired character. This splits the data into separate columns - devoid of the specified delimiter.

As it turns out, I end up doing this often because it is an excellent way to import data in .db/.dbii formats into FM.

Ken

[lester]

Well, that would be fine, but many of the restaurant names have - in the names like Bar-B-Que etc. That would really mess up the names of thousands of my listings since these are all Barbecue restaurants. It is crazy how many ways barbecue can be spelled.

Bar,B,Que would look pretty strange.

[lester]

That method for adding the comma after the state abbreviation didn't work properly either. I was doing AL for Alabama, and it added a comma after the letters al in every word that contained that combination.

[The Bridge]

In the case of hyphenated restaurant names, unless there are spaces surrounding the hyphen, it will be unaffected by the replace. That's why I suggest replacing all space-hyphen-space combinations with commas, i.e. a space followed by a hyphen followed by a space. I would also suggest, regardless of what method you ultimately choose, that you go through your data and double-check the results. A record that has Bar in the name, b in the address and Que in the City fields will be relatively easy to spot.

Best of luck to you.

[lester]

Thanks Everybody,

I think I have it figured out now. I am still having alot of problems with the State abbreviation adding a comma through the replacement option. For some weird reason, it will add the comma to any possible same letters that happen to be next to each other at the end or beginning of any word.

The only way around this seems to be to manually add a comma after every state abbreviation.

I am also having some problems with excel scrambling the zip code and phone numbers. Usually, they will just disappear.

Anyway, I think I can do this now. It will still be very tedious and slow, but maybe a bit faster.

Thanks again.

[Kundinger]

Hello 'lester',

YES... you can 'move' your data from Word to FileMaker without 'retyping'. You have two basic ways to do this:

A. Clean up the data in Word and import into FileMaker

- or -

B. Import the data into FileMaker and clean up there

A. The Word Advantage -- this way requires you to perform, basically, three steps:

Step A1. In Word, "Find/Replace" all your 'data separators' with the "TAB" character

Step A2. In Word, "Save As..." file type "Text with Line Breaks", named "Data.txt"

Step A3. In FileMaker, "Import" the "Data.txt" file

B. The FileMaker Cleanup -- this way requires you to perform, basically, three steps:

Step B1. In Word, "Save As..." file type "Text with Line Breaks", named "Data.txt"

Step B2. In FileMaker, "Import" the "Data.txt" file

Step B3. In FileMaker, create calculation fields that will 'separate' the data into their appropriate fields

Neither of these requires the use of Excel. Either way has advantages and disadvantages. For you though, I would recommend "A. The Word Advantage". It would be more complicated for you to create complex calculations in FileMaker to 'separate' the data in to fields.

That said... each step is described in basic terms. It is