Mail Merge using CSV file

[Michelle]

I have a CSV file with content as follows
H, Ben, Smith, 5 North Lane, Some Suburb, 3131
D, Bicycle, $120
H, Jill, Brown, 63 Heather Street, Blue Suburb, 546
D, Skateboard, $45
D, Computer, $300

There is one H (Header) row for each person, but each
person may have more than one D (Detail) row. I need to
mail merge this data into a Word letter so that I can
send each person a letter with the items in the Detail
rows listed. There is no limit to how many Detail rows a
person may have.

The CSV file is generated automatically by another
application and does not include a traditional header
(Name, Surname etc) for the document.

Looking forward to hearing ANY ideas,
Michelle.

 

[Graham Mayor]

This is not going to be easy
The format you have is not a format Word will recognize as a data file.
If you open it in Word, you can get close by running the following macro

Sub CSVToTable()
Selection.HomeKey Unit:=wdStory
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "(^13)D,"
.Replacement.Text = "\1,,,,,"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.HomeKey Unit:=wdStory
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "H, (*[0-9]{1,})^13"
.Replacement.Text = "\1,,^p"
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.WholeStory
Selection.ConvertToTable Separator:=wdSeparateByCommas, NumColumns:=7, _
NumRows:=5, AutoFitBehavior:=wdAutoFitFixed
With Selection.Tables(1)
.Style = "Table Grid"
.ApplyStyleHeadingRows = True
.ApplyStyleLastRow = True
.ApplyStyleFirstColumn = True
.ApplyStyleLastColumn = True
End With
End Sub

But there will still be some tidying up to do, as you need to fill the empty
cells with the data from the addresses above them to end up with

Ben, Smith, 5 North Lane, Some Suburb, 3131, ,
Ben, Smith, 5 North Lane, Some Suburb, 3131, Bicycle, $120
Jill, Brown, 63 Heather Street, Blue Suburb, 546, ,
Jill, Brown, 63 Heather Street, Blue Suburb, 546, Skateboard, $45
Jill, Brown, 63 Heather Street, Blue Suburb, 546, Computer, $300
etc

If you are lucky, some kind soul will come up with a macro to do this and it
may be easier to do it in Excel than Word. Unfortunately I do not have
sufficient time available to out to this task at present.

Next add a title row to the table and save the resulting data file.

Even then your woes are not over as Word is not very good at the type of
merge you are considering. Cindy has some tips on her web site at
http://homepage.swissonline.ch/cindymeister/mergfaq1.htm#ComplexMerg which
should help.


--
<>>< ><<> ><<> <>>< ><<> <>>< <>>< ><<>
Graham Mayor - Word MVP

Web site www.gmayor.com
Word MVP web site www.mvps.org/word
<>>< ><<> ><<> <>>< ><<> <>>< <>>< ><<>

 

[Doug Robbins - Word MVP]

I developed an answer for you in response to your identical post in
microsoft public.word.vba.beginners and now I find that fellow MVP Graham
Mayor has also spent some time providing you with information in response to
your post in the mailmerge newsgroup.

Please do not post the same question separately to multiple newsgroups as it
can result in people wasting their time developing an answer to a post that
has already been answered by somebody else.

--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP