Errors in Merge Document

[ReneeC]

Hi,

I’m working on the culmination of a an SQL/VB.NET application. It’s output
is a directory actually a repetitive manual. Part of the application
generates a CSV file, which I think is in the right format, for Word1003.

The CSV File data looks looks like this:

General
Service|Description|Paragraph1|Paragraph1|Paragraph3|Narrative|Service
Detail|Organization|Phone|Ages|Languages|Eligibility
Abuse and Neglect Services|||||||||||-1
|Help for an abused or neglected child||||||||||-1
||The organizations listed below provide services for abused or neglected
children and their families.|||||||||-1
|||For each of the organizations listed below you will find information
about the ages they serve, the languages spoken and eligibility
criteria.||||||||-1
||||More complete information about these organizations's location, contact
person, hours of operation and a description of their programs can be found
in Part II - Directory of Organizations starting on page xx.|||||||-1
|||||More complete information about these organization’s locations, contact
person, hours of operation, fees charged and a description their programs can
be found in part II, the directory of organizations starting on page
PAGENUM.||||||-1
||||||General|||||-1
|||||||Associates For Renewal In Education|(555) 643-9616|1-6; 6-11;
13-1(Also adults)|English;|All
|||||||Center for Adoptive Families (of AdoptionsTogether, Inc.)*|(555)
639-1900|0-6; 6-11; 13-14|Portuguese; Spanish|Children and their families
affected by adoption or foster care
|||||||Center for Child Protection and Family Support, Inc.|(555)
566-6165|6-6; 11; 13-16|English|Child victims of neglect, physical abuse,
sexual abuse or with a report from the police regarding child abuse or
neglect. ;
|||||||Smith China Center for Youth & Families|(555) 536-6050|1-14 and their
parents|English|all
|||||||Child and Family Services Agency*|(555) 661-6100| 0-6; 6-11; 13-14
(Also young adults)|English; Spanish|Children who have been or are at risk
of abuse or neglect
|||||||Children's Nice Medical Center- Child and Adolescent Protection
Center|(555) 446-6950|0-6; 6-11; 13-14|English; Interpreter services may be
available|Medicaid enrollees only
|||||||Blue Heights/Green Family Support Collaborative|(555) 514-6636| 0-6;
6-11; 13-14;|English|Families with minor children
|||||||Commision on Mental Health Service – South East Family Center*|(555)
663-1064|6-11|English; Spanish; Portuguese; American Sign Language|All
|||||||ACITY Department of Recreation and Parks- Day Care and Head Start
Branch*|(555) 566-6116|6 weeks - 11 years old-5 for Head Start|English;
Spanish|ACITY residence and employed/ training for minimum of 10 weeks
|||||||ACITY Department of Recreation-Youth Intervention Division*|(555)
943-6561|6-11; 13-14|English; Spanish|ACITY residence
|||||||ACITY Psychological Association Referral Service|(555) 336-5556|6-11;
13-14|English|All callers
|||||||ACITY Rape Crisis Center|(555) 131-0649| 6-11; 13-14 (Also
adults)|English; Spanish; American Sign Language|All persons who have
experienced sexual violence and their friends and families.
|||||||ACITY Superior Court- Social Service Division*|(555) 504-1944|0-6;
6-11; 13-14|English; Spanish|Individuals under court supervision
|||||||Family Assistance in Coping with Trauma and Stress (F.A.C.T.S,
PLLC)|(555) 566-5660| 0-6; 6-11; 13-14;|English|All
|||||||For Love of Children|(555) 661-4646|0-6; 6-11; 13-14|English|Shaw
residence; referrals by Child and Family Service Administration or by ACITY
Public Schools
|||||||Healthy Babies Project|(555) 396-1409|0-1 and other siblings in
family.|English|Residents of Wards 5 & 6 and homeless pregnant women, teen
mothers and fathers

The Merge document right now looks like this:

«General_Service»«Next Record»
«Description»«Next Record»

«Paragraph1»
«Paragraph2»
«Paragraph3»
«Narrative»«Next Record»

«Service_Detail»«Next Record»

«Organization» «Phone»

Ages: «Ages»
Language(s): «Languages»
Eligibility: «Eligibility»



When the merge is run on the surface things look OK however they really
aren’t.

The way the CSV file is organized is:

General Service #1
Description
Service Detail
Org A
Org B
Org C
General Service #1
Description
Service Detail
Org Q
Org L
Org M
General Service #3
Description
Service Detail
Org R
Org S
Org T

But the Output is giving me

General Service #1
Description
Service Detail
Org A
Org L
Org T (I think)

So obviously there’s a problem in my Merge Document or the format of my CSV
File.

Could anyone help me with this? One of the troubles for for is that there
isn’t any technical documentation on how this works.

Thanks

 

[Peter Jamieson]

OK, it's a little difficult to see precisely how your csv data is organised
because when you post text via these groups, it's difficult to tell where
line breaks and so on are supposed to be. What's more it's a bit difficult
to match up your data with the list of 18 fields you have below.

So...

It looks as if you are using | as your field delimiter, but what are you
using as a "record delimiter"? (Typically, it's a "carriage return/line
feed" combination, but it doesn't absolutely have to be).

You don't appear to have a row containing the column names for the data
source. Is that the case, or
a. do you have a separate "header source" (which is possible in Word, but
not well-documented these days) or
b. have you left that out to simplify your description or
c. is the first part of your text actually a header?

Could you re-do your example showing the data in just /one/ record?

Typically what you need in a csv data source is
a. a sequence of records, each terminated by a "record delimiter" that Word
can recognise
b. each record should consist of the same number of columns, each except
the last terminated by a "field delimiter"
c. there are special rules when a value in a column contains the characters
being used as the record delimiter (and/or possibly CR and/or LF
characters), the field delimiter, or the "text delimiter", which can
typically only be a single or double quote character.

And you're right: the rules aren't particularly well documented

Peter Jamieson

 

[ReneeC]

Hi Peter,

Thatnk for your time and attention.

It looks as if you are using | as your field delimiter, but what are you
using as a "record delimiter"? (Typically, it's a "carriage return/line
feed" combination, but it doesn't absolutely have to be).

It's a VBcrfl

You don't appear to have a row containing the column names for the data
source.

Gosh I don't know why the example came out that way, Header row is:

General
Service|Description|Paragraph1|Paragraph2|Paragraph3|Narrative|Service
Detail|Organization|Phone|Ages|Languages|Eligibility

But the editor is distorting things the begining of the header record looks
like this:

General Service|Description|Paragraph1|

Sitting back and looking at my merge documemt I realize what I want it to do.

I want it to process organization data until there is not more and then do
the next General Service.




Is that the case, or

a. do you have a separate "header source" (which is possible in Word, but
not well-documented these days) or
b. have you left that out to simplify your description or
c. is the first part of your text actually a header?

The first part of the text was the header file BUT it was distorted by the
Editor.

Could you re-do your example showing the data in just /one/ record?

Typically what you need in a csv data source is
a. a sequence of records, each terminated by a "record delimiter" that Word
can recognise
Check
b. each record should consist of the same number of columns, each except
the last terminated by a "field delimiter"
Check

c. there are special rules when a value in a column contains the characters
being used as the record delimiter (and/or possibly CR and/or LF
characters), the field delimiter, or the "text delimiter", which can
typically only be a single or double quote character.

OK... There is client data here and the data has commas and that that reason
for the "|" field separator.

And you're right: the rules aren't particularly well documented

Peter Jamieson

It's not the 'rules' as much as it is the model. It seems to be written for
users which is understandable but not developers.

if it's "easy to use" - and documentation is from the user's pov
operationally, it's difficult for the developer to know what she is doing.

Renee

 

[ReneeC]

I have to apologize. The record data was old.

All records have the same number of fields.

This is a General Service Record:

Abuse and Neglect Services|||||||||||

This is a description record:

|Help for an abused or neglected child||||||||||

This is a Service Detail Record:

||||||General|||||

This an Organization record

|||||||Associates For Renewal For Blah|(555) 483-9424|2-6; 7-12; 13-2(Also
adults)|English;|All

All records are Terminated by a crlf.

 

[Peter Jamieson]

Hi Renee,

OK, following this and you're other message, I've now got some data into a
..csv file and there doesn't seem to be any problem with the data format, in
itself.

However, now I see what you have and your comment...

Sitting back and looking at my merge documemt I realize what I want it to
do.

I want it to process organization data until there is not more and then do
the next General Service.

....I can tell you that the reality is that Word is not really designed to do
this sort of thing, which I would say is really the kind of
"grandparent/parent/child" type report you might expect to be able to create
in the report generator that comes with a decent RDBMS. For example, you
might be able to do it using the Access Report generator, or if you're using
SQL Server, SQl Server Reporting Services.

That said, there are ways to do some of this stuff using a MailMerge - see
for example

http://homepage.swissonline.ch/cindymeister/mergfaq1.htm#DBPic

http://support.microsoft.com/default.aspx?scid=kb;en-us;211303

http://www.knowhow.com/Guides/CompoundMerges/CompoundMerge.htm

Also, if you need Word-format output but Mailmerge doesn't quite cut it, it
sounds to me as if your requirement might be sufficiently simple and
straightforward for you to generate the entire document using VBA, reading
the data either from the csv file or directly from your SQL source using ADO
and inserting it directly into the output document. Difficult to tell.

It's not the 'rules' as much as it is the model. It seems to be written
for
users which is understandable but not developers.

if it's "easy to use" - and documentation is from the user's pov
operationally, it's difficult for the developer to know what she is doing.

I'm not sure I'd be so generous about the "ease of use"

Peter Jamieson

 

[ReneeC]

Hi Peter,

I've tried it use Word Automation which I've never done before.

If I was doing right it became very complex very fast.

What's the advantage of VBA (Oh no ...anything but that) over VB.NET

When I tried it, things became very complex very fast and Word really seemed
slow and to take forever and I have a very fast machine.

 

[Peter Jamieson]

Hi Renee,

VBA is probably a red herring. Typically I don't know of any reason why you
shouldn't do things in VB.NET rather than VBA, but...
a. I don't know for sure in any given case
b. some of the articles I directed you to would certainly have assumed you
are using VBA.
c. in short, you may have to translate some of the code I suggested into
VB.NET.
d. anything you do in Word from VB.NEt has at least to use the Word
"Interop Assembly", a barrier that may well make some things extremely
inefficient. I annot say I'm familiar with crossing that particular barrier
but I am fairly sure it makes a difference exactly how you go about doing
it.

Peter Jamieson

 

[ReneeC]

Thank you Peter,

I know about Interop. This is about the third abortive attemp to format this
document. The first was Word Interop. Most developers including myself do not
like it. It's slow and ponderous and included in the name is the fact that
it's unmanaged.
Word Interop is not well documented.

Since my first abortive attempt. I found a good Knowledge base example that
taught me some things:

http://support.microsoft.com/kb/220607

However the knowledge artice has a bug in it and it won't run.

This is the correct code or at least code that will run:

' Create a data source at C:\DataDoc.doc containing the field data.

wrdDoc.MailMerge.CreateDataSource(Name:="C:\DataDoc.doc", _

HeaderRecord:="FirstName, LastName, Address, CityStateZip")

' Open the file to insert data.

wrdApp.Documents.Add("C:\DataDoc.doc")

wrdDataDoc = wrdApp.Documents(wrdApp.Documents.Count - 1)

------------------------------

I'm going to hate going back to interop. In this task, it's easy to get lost
quickly.

Thank you for your time and most valued and appreciated expertise.

Renee

 

[Peter Jamieson]

Hi Renee,

I've gone back to square one and realised that I've probably fundamentally
misunderstood your application and that it's probably much simpler than I
thought.

When i get all the data in the format it should be in, it seems to me that
what you probably need is
a. to use a catalog (directory) type merge
b. to avoid using { NEXT } fields altogether. There do not seem to be
enough of them to deal with the number of "heading" type records at the
beginning of your file anyway.
c use one IF field to detect which record type you are looking at, and
insert the necessary fields and text in the appropriate result.

e.g. something like

{ IF "{ MERGEFIELD General_Service }" = "" "" "
{ MERGEFIELD General_Service }"
}{ IF "{ MERGEFIELD Description }" = "" "" "
{ MERGEFIELD Description }
" }{ IF "{ MERGEFIELD Paragraph1 }" = "" "" "
{ MERGEFIELD Paragraph1 }"
}{ IF "{ MERGEFIELD Paragraph2 }" = "" "" "
{ MERGEFIELD Paragraph2 }"
}{ IF "{ MERGEFIELD Paragraph3 }" = "" "" "
{ MERGEFIELD Paragraph3 }"
}{ IF "{ MERGEFIELD Narrative }" = "" "" "
{ MERGEFIELD Narrative }
" }{ IF "{ MERGEFIELD Service_Detail }" = "" "" "
{ MERGEFIELD Service Detail }
"}{ IF "{ MERGEFIELD Organization }" = "" "" "
{ MERGEFIELD Organization } { MERGEFIELD Phone }

Ages: { MERGEFIELD Ages }
Language(s): { MERGEFIELD Languages }
Eligibility: { MERGEFIELD Eligibility}" }

(Every pair of {} need to be the special field braces you can insert using
ctrl-F9). you can insert paragraph marks inside the IF field results to get
the paragraph spacing you want, as I have tried to indicate).

That may not be quite right but it should give you the general idea.

Peter Jamieson