data manipulation

[Michael Myett]

I am working on WinXP and using FileMaker 6.04

I have a fairly large amount of data in a text file, Massachusetts
Insurance Agents, that looks like the following:

Name
Company Name
Address
City, State Zip
Phone
Fax
email address
web site

Name
Company Name
Address
City, State Zip
Phone
Fax
email address
web site

...... this list view of data continues for thousands of records.

My goal is to move this text file data into a contact database. I have
tried manipulating the data into some sort of table format that can be
imported into FileMaker, with no success.

I have a sickening feeling that this is just going to be a lot of time
consuming work, but thought it might be worth an ask if anyone has had
some level of success doing something like this before.

Even partial automation would be a blessing at this point.

Many thanks in advance for any help.

Michael Myett

[Helpful Harry]

In article <dEHmb.6956$1C5.3927@nwrdny02.gnilink.net>, Michael Myett
<michael.myett@verizon.net> wrote:

> I am working on WinXP and using FileMaker 6.04
>
> I have a fairly large amount of data in a text file, Massachusetts
> Insurance Agents, that looks like the following:
>
> Name
> Company Name
> Address
> City, State Zip
> Phone
> Fax
> email address
> web site
>
> Name
> Company Name
> Address
> City, State Zip
> Phone
> Fax
> email address
> web site
>
> ..... this list view of data continues for thousands of records.
>
> My goal is to move this text file data into a contact database. I have
> tried manipulating the data into some sort of table format that can be
> imported into FileMaker, with no success.
>
> I have a sickening feeling that this is just going to be a lot of time
> consuming work, but thought it might be worth an ask if anyone has had
> some level of success doing something like this before.
>
> Even partial automation would be a blessing at this point.
>
> Many thanks in advance for any help.
>
> Michael Myett

First: MAKE A COPY OF THE FILE!!!! And only "play" with the copy, NOT
the original.


If the file isn't "too" big, then you could open it up into something
like MS Word and do a couple of Find / Replaces to transform the data
into something better.

First Find all 'CR CR' (ie. two line returns, just copy them from the
document and paste them into the Find window) and replace them with
something not likely to be elsewhere in the text (eg. *-*-*).

Next Find all 'CR' (ie. one line return) and replace it with a Tab
(again, copy / paste both of these).

Lastly Find all *-*-* and re-replace them with a single 'CR'.

'Save As' the file TO A NEW NAME as Text format. You've now got a text
file using tabs to separate field data and returns to separate each
record ... and that should import into a FileMaker database with no
problems.


*BUT*
This will only work if the original lines in the text file (eg.
address) don't already contain tabs in the middle. Otherwise you'll
need to do some more Find / Replace playing to get rid of those first.

If the original lines contain line returns in the middle, then you're a
bit stuck and may have to go through manually removing those first.



It might take a while to Find / Replace and import if the file is
REALLY big - you might want to also do a Save As to a new filename in
between each step if you're using Word since it's notoriuos for
crashing just when you don't want it to.



Helpful Harry
"Just trying to help whenever I can." :o)

[Michael Myett]

Harry,

Thanks a million.

Best regards,
Michael Myett

Helpful Harry wrote:

> In article <dEHmb.6956$1C5.3927@nwrdny02.gnilink.net>, Michael Myett
> <michael.myett@verizon.net> wrote:
>
>

>>I am working on WinXP and using FileMaker 6.04
>>
>>I have a fairly large amount of data in a text file, Massachusetts
>>Insurance Agents, that looks like the following:
>>
>>Name
>>Company Name
>>Address
>>City, State Zip
>>Phone
>>Fax
>>email address
>>web site
>>
>>Name
>>Company Name
>>Address
>>City, State Zip
>>Phone
>>Fax
>>email address
>>web site
>>
>>..... this list view of data continues for thousands of records.
>>
>>My goal is to move this text file data into a contact database. I have
>>tried manipulating the data into some sort of table format that can be
>>imported into FileMaker, with no success.
>>
>>I have a sickening feeling that this is just going to be a lot of time
>>consuming work, but thought it might be worth an ask if anyone has had
>>some level of success doing something like this before.
>>
>>Even partial automation would be a blessing at this point.
>>
>>Many thanks in advance for any help.
>>
>>Michael Myett

>
>
> First: MAKE A COPY OF THE FILE!!!! And only "play" with the copy, NOT
> the original.
>
>
> If the file isn't "too" big, then you could open it up into something
> like MS Word and do a couple of Find / Replaces to transform the data
> into something better.
>
> First Find all 'CR CR' (ie. two line returns, just copy them from the
> document and paste them into the Find window) and replace them with
> something not likely to be elsewhere in the text (eg. *-*-*).
>
> Next Find all 'CR' (ie. one line return) and replace it with a Tab
> (again, copy / paste both of these).
>
> Lastly Find all *-*-* and re-replace them with a single 'CR'.
>
> 'Save As' the file TO A NEW NAME as Text format. You've now got a text
> file using tabs to separate field data and returns to separate each
> record ... and that should import into a FileMaker database with no
> problems.
>
>
> *BUT*
> This will only work if the original lines in the text file (eg.
> address) don't already contain tabs in the middle. Otherwise you'll
> need to do some more Find / Replace playing to get rid of those first.
>
> If the original lines contain line returns in the middle, then you're a
> bit stuck and may have to go through manually removing those first.
>
>
>
> It might take a while to Find / Replace and import if the file is
> REALLY big - you might want to also do a Save As to a new filename in
> between each step if you're using Word since it's notoriuos for
> crashing just when you don't want it to.
>
>
>
> Helpful Harry
> "Just trying to help whenever I can." :o)

[Fred]

Another option, if you are using MS Word, is to use the Convert to Text
feature (Table menu). Assuming EVERY record has the same # of lines as
listed below, select all the text and choose Convert Text to Table. Set the
number of columns to 9 (one for each line, including the blank line) and
voila. You can then import to FMP. The one problem might be the City,
State Zip line, as you will probably want those in separate fields. You can
reformat that field in FMP with a script and "Pattern Count" "Right()"
script steps.

Just a thought.

Fred

On 10/25/03 8:59 PM, in article dEHmb.6956$1C5.3927@nwrdny02.gnilink.net,
"Michael Myett" <michael.myett@verizon.net> wrote:

> I am working on WinXP and using FileMaker 6.04
>
> I have a fairly large amount of data in a text file, Massachusetts
> Insurance Agents, that looks like the following:
>
> Name
> Company Name
> Address
> City, State Zip
> Phone
> Fax
> email address
> web site
>
> Name
> Company Name
> Address
> City, State Zip
> Phone
> Fax
> email address
> web site
>
> ..... this list view of data continues for thousands of records.
>
> My goal is to move this text file data into a contact database. I have
> tried manipulating the data into some sort of table format that can be
> imported into FileMaker, with no success.
>
> I have a sickening feeling that this is just going to be a lot of time
> consuming work, but thought it might be worth an ask if anyone has had
> some level of success doing something like this before.
>
> Even partial automation would be a blessing at this point.
>
> Many thanks in advance for any help.
>
> Michael Myett
>