Mail Merge Second Record in DataSource and Mail Merge formatting

Aspose.Words Product Family
1

I have a word document that has a merge data source already linked. I am trying to create a document that shows the data from the second merge record, but every time I try it always outputs the first record. What am I doing wrong? Here is my code:

docx = new Aspose.Words.Document(this.MergeFile);
docx.MailMergeSettings.ActiveRecord = 2;
docx.MailMergeSettings.ViewMergedData = true;
docx.MailMergeSettings.Destination = Aspose.Words.Settings.MailMergeDestination.NewDocument;
docx.MailMergeSettings.MainDocumentType = Aspose.Words.Settings.MailMergeMainDocumentType.MailingLabels;
docx.MailMergeSettings.DataType = Aspose.Words.Settings.MailMergeDataType.Native;
docx.Save(pdfstream, Aspose.Words.SaveFormat.Pdf);

Also, we are having issues getting the formatting to come out correctly. When previewed in Microsoft Word all of the addresses and records look fine. When using the Aspose library, there is a comma and additional white space appearing in the outputted document. (see attachment).

Any help would be greatly appreciated. Thank you.

Attachment Descriptions:
sampleMergeData.doc: The datasource file containing 3 records
sampleMergeDocument.doc: The merge file with a datasource link to the sampleMergaData.doc
outputFromAspose.png: The outputted document when using the Aspose library. You can see the extra comma as well as additional white space.
previewInWord1.png: A preview of how the first record looks in Microsoft word. This is how we would like it to be outputted using the Aspose library.
previewInWord2.png: A preview of how the third record looks in Microsoft word. This is how we would like it to be outputted using the Aspose library.

2

Hi Chris,

Thanks for your inquiry. I have attached a couple of documents here for your reference. Please try executing the following code:

Document doc = new Document(MyDir + @"Doc1.docx");
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\\Temp\\Book1.xlsx;Mode=Read;Extended Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=\"\";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False";
string query = "SELECT * FROM `Sheet1$`";
MailMergeSettings mms = doc.MailMergeSettings;
mms.ActiveRecord = 3;
mms.MainDocumentType = MailMergeMainDocumentType.MailingLabels;
mms.DataType = MailMergeDataType.Native;
mms.DataSource = @"C:\Temp\Book1.xlsx";
mms.ConnectString = connectionString;
mms.Destination = MailMergeDestination.NewDocument;
mms.Query = query;
mms.LinkToQuery = false;
mms.ViewMergedData = true;
doc.Save(MyDir + @"out.docx");

I hope, this helps in understanding how you can achieve this.

Best regards,

3

Thanks for the response. Based on your input I tried the below code but was unsuccessful. It is still only generating a document with the first record. Your example appears to be using an excel file as the datasource. With our document the data is stored in a .docx file (see the SampleMergeData.docx for an example). How would we be able to do the merge using a .docx file? Thanks

docx = new Aspose.Words.Document(this.MergeFile);
string mergedatapath = docx.MailMergeSettings.DataSource;
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=";
connectionString += mergedatapath;
connectionString += ";Mode=Read;Extended Properties="HDR=YES;IMEX=1;";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don’t Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False";
string query = "SELECT * FROM Sheet1$";

Aspose.Words.Settings.MailMergeSettings mms = docx.MailMergeSettings;
mms.ActiveRecord = 3;
mms.MainDocumentType = Aspose.Words.Settings.MailMergeMainDocumentType.MailingLabels;
mms.DataType = Aspose.Words.Settings.MailMergeDataType.Native;
mms.DataSource = mergedatapath;
mms.ConnectString = connectionString;
mms.Destination = Aspose.Words.Settings.MailMergeDestination.NewDocument;
mms.Query = query;
mms.LinkToQuery = false;
mms.ViewMergedData = true;

docx.MailMergeSettings = mms;

docx.Save(pdfstream, Aspose.Words.SaveFormat.Pdf);
pdfstream.Position = 0;
4

Hi Chris,

Thanks for your inquiry.

I have logged a task in our issue tracking system for our development team to investigate if it is possible to pass a Word document as data source to MailMergeSettings.DataSource or not. Your ticket number is WORDSNET-10406. Your request has also been linked to this task and you will be notified as soon as it is worked out. Sorry for the inconvenience.

Best regards,

5

Hi Chris,

Thanks for being patient. We suggest you please read/parse Word document containing the ‘data’ using Aspose.Words, execute mail merge on template document again using Aspose.Words and finally convert to PDF. Please see the following draft code:

Document docData = new Document(MyDir + @"sampleMergeData.doc");
Document doc = new Document(MyDir + @"sampleMergeDocument.doc");
// Read and build datatable from docData
DataTable dataTable = new DataTable("Data");
Paragraph columnNames = docData.FirstSection.Body.FirstParagraph;
string[] columnNamesSplits = columnNames.ToString(SaveFormat.Text).Split(new char[] { ',' });
foreach (string name in columnNamesSplits)
    dataTable.Columns.Add(new DataColumn(name.Trim().Replace("\"", "").Replace("\"", "")));
for (int i = 1; i < docData.FirstSection.Body.Paragraphs.Count; i++)
{
    Paragraph row = docData.FirstSection.Body.Paragraphs[i];
    string[] rowSplits = row.ToString(SaveFormat.Text).Split(new char[] { ',' });
    int count = 0;
    DataRow dataRow;
    dataRow = dataTable.NewRow();
    foreach (string name in rowSplits)
    {
        dataRow[count++] = name.Trim();
    }
    dataTable.Rows.Add(dataRow);
}
doc.MailMerge.Execute(dataTable);
doc.Save(MyDir + @"out.pdf");

I hope, this helps.

Best regards,