Embed datasource in document

mstride · August 23, 2017, 9:40pm

Hi All,

I’m trying to setup a scenario like this:

We provide the user with an empty word document or they upload their own as a starting point. We then attach the datasource to it and allow them to download a version of it with the datasource attached. Since the document now has the datasource attached, they can place the Mail merge fields were they want to. This is then saved as a template. This datasource at this point is only really used temporarily so that word is aware of what Mail merge fields are available.
The mail merge is run using the document from step 1. (With a freshly generated data source)

I can get this to work using aspose.words by setting the datasource to an external file. However in order to do so it requires that the user download both the document template file and the datasource file. I was wondering is it possible to embed a datasource or field list within a word document so the user does not need to download two separate files to have the field list available in the document template? Or is this an MS word limitation which is not possible?

Thank you

tilal.ahmad · August 24, 2017, 2:36pm

@mstride

Thanks for your inquiry. You may attach data source to word document using MailMergSettings. Please check the following code example. Hope, it helps.

Document doc = new Document();


string dataSource = @"C:\Temp\datasource.txt";


doc.MailMergeSettings.DataType = MailMergeDataType.TextFile;

doc.MailMergeSettings.MainDocumentType = MailMergeMainDocumentType.FormLetters;

doc.MailMergeSettings.DataSource = dataSource;

doc.MailMergeSettings.Query = string.Format(@"SELECT * FROM {0}", dataSource);


Regex whiteSpaceRegex = new Regex(@"\s+");

using (StreamReader reader = new StreamReader(dataSource, Encoding.UTF8))

{

string firstLine = reader.ReadLine();

string[] fieldNames = firstLine.Split(new char[] { '\t' }, StringSplitOptions.RemoveEmptyEntries);


for (int i = 0; i < fieldNames.Length; 
i++)

{

OdsoFieldMapData mapData = new OdsoFieldMapData();

mapData = new OdsoFieldMapData();


mapData.Column = i;


mapData.MappedName = fieldNames[i];


mapData.Name = whiteSpaceRegex.Replace(fieldNames[i], "_");


mapData.Type = OdsoFieldMappingType.Column;


doc.MailMergeSettings.Odso.FieldMapDatas.Add(mapData);

}

}


doc.Save("Template.docx");

mstride · August 24, 2017, 9:46pm

Thank you for the sample code.

This works great however it still requires the datasource file (“C:\Temp\datasource.txt”) to be present on the machine. I am hoping to make it so the user needs only to download a single template file, rather then two files.

I have since noticed however noticed that the datasource property seems to also work correctly with web urls. Using the approach based on your sample code with a datasource such as "http://machine-name/data/datasource.txt" might make it possible to achieve what I am looking for. Are there any limitations so this I should be aware of?

tilal.ahmad · August 25, 2017, 4:36am

@mstride,

Thanks for your feedback. It is good to know that you have managed to accomplish your requirements. There is no known issue to use files from web URL. You may also try to share data source file and Word template as a single ZIP file with user.

mstride · August 27, 2017, 10:47pm

Are there any links to details as to which file formats the Datasource property supports?

Is it possible to suppress the word dialog which asks which column separator I want to use? I tried setting mms.Odso.ColumnDelimiter = ‘,’ but that did not seem to work.

Also does the datasource support nested tables? By this I mean, taking the example from the help documentation would it be possible to get word to recognize the special merge fields TableStart:Order and TableEnd:Order for use with the nested mail merge (https://docs.aspose.com/words/net/nested-mail-merge-with-regions/)

Thank you

tilal.ahmad · August 28, 2017, 5:50am

@mstride

Thanks for your inquiry.

Please check MailMergeDataType API Reference for details.

I have tested the scenario with following code with a text data source file containing following text and unable to notice any MS Word dialogue for column separator. Please share your sample data source file here. We will look into it and will guide you accordingly.

Field1,Field2,Field3,Field4,Field5

string dataSource = @"E:\data\datasource.text";
Document doc = new Document();
doc.MailMergeSettings.DataType = MailMergeDataType.TextFile;
doc.MailMergeSettings.MainDocumentType = MailMergeMainDocumentType.FormLetters;
doc.MailMergeSettings.DataSource = dataSource;
doc.MailMergeSettings.Query = string.Format(@"SELECT * FROM {0}", dataSource);
Regex whiteSpaceRegex = new Regex(@"\s+");

using (StreamReader reader = new StreamReader(dataSource, Encoding.UTF8))
{

    string firstLine = reader.ReadLine();
    string[] fieldNames = firstLine.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);

    for (int i = 0; i < fieldNames.Length; i++)
    {

        OdsoFieldMapData mapData = new OdsoFieldMapData();
        mapData = new OdsoFieldMapData();
        mapData.Column = i;
        mapData.MappedName = fieldNames[i];
        mapData.Name = whiteSpaceRegex.Replace(fieldNames[i], "_");
        mapData.Type = OdsoFieldMappingType.Column;
        doc.MailMergeSettings.Odso.FieldMapDatas.Add(mapData);

    }

}

doc.Save(@"E:/Data/MailMergeTemplate.docx");

It should work. However, if you are facing some issue then please let us know with your sample code, data source file and expected Word document as a ZIP file. We will look into these and will guide you accordingly.

mstride · August 28, 2017, 9:58pm

Thankyou very much, using the sample code provided I can use a csv datasource and no longer get word asking me which field separator to use.

Regarding the nested tables, I get the same issue when I try to attach the datasource to the template manually in word so I’m wondering if this is an MS word limitation and there may not be much that can be done to solve this. What happens is that the datasource contains a field such as TableStart:Order which I hope to make available as a template field. However MS word converts TableStart:Order to TableStartOrder (Removing the “:” character).

The code for this is based largely on the sample you provided. I have also attached my datasource file and resulting template. Is there a way to somehow escape the “:” character in the datasource file?

AsposeMailMerge.zip (10.0 KB)

Document doc2 = new Document();

MailMergeSettings mms = new MailMergeSettings();

mms.MainDocumentType = MailMergeMainDocumentType.FormLetters;
mms.DataType = MailMergeDataType.TextFile;
mms.DataSource = @"data.csv";

mms.Query = "SELECT * FROM [" + "data" + "]";
mms.LinkToQuery = false;
mms.ViewMergedData = false;

Regex whiteSpaceRegex = new Regex(@"\s+");

using (StreamReader reader = new StreamReader(@"D:\AsposeMailMerge\data.csv", Encoding.UTF8))
{
    string firstLine = reader.ReadLine();
    string[] fieldNames = firstLine.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
    for (int i = 0; i < fieldNames.Length; i++)
{
        OdsoFieldMapData mapData = new OdsoFieldMapData();
        mapData = new OdsoFieldMapData();
        mapData.Column = i;
        mapData.MappedName = fieldNames[i];
        mapData.Name = whiteSpaceRegex2.Replace(fieldNames[i], "_");
        mapData.Type = OdsoFieldMappingType.Column;
        doc.MailMergeSettings.Odso.FieldMapDatas.Add(mapData);
    }
}

doc2.MailMergeSettings = mms;
doc2.Save(@"D:\AsposeMailMerge\Template_Actual.docx", SaveFormat.Docx);

tilal.ahmad · August 29, 2017, 7:00am

@mstride

Thanks for your inquiry. Yes, it seems MS Word removes the “:” from fields in datasoruce and Aspose.Words is showing same behavior when using MailMergeDataType.Native. As a workaround to accomplish your requirements, you can post process the nested table template and rename required Mail Merge Fields. Please check following example to rename the Mail Merge Field.

Rename Mail Merge Field Example

Document doc2 = new Document();

MailMergeSettings mms = new MailMergeSettings();

mms.MainDocumentType = MailMergeMainDocumentType.FormLetters;
mms.DataType = MailMergeDataType.Native;
mms.DataSource = @"D:\Downloads\AsposeMailMerge\data.csv";

mms.Query = "SELECT * FROM [" + "data" + "]";
mms.LinkToQuery = false;
mms.ViewMergedData = false;

Regex whiteSpaceRegex = new Regex(@"\s+");

using (StreamReader reader = new StreamReader(@"D:\Downloads\AsposeMailMerge\data.csv", Encoding.UTF8))
{
    string firstLine = reader.ReadLine();
    string[] fieldNames = firstLine.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
    for (int i = 0; i < fieldNames.Length; i++)
    {
        OdsoFieldMapData mapData = new OdsoFieldMapData();
        mapData = new OdsoFieldMapData();
        mapData.Column = i;
        mapData.MappedName = fieldNames[i];
        mapData.Name = whiteSpaceRegex.Replace(fieldNames[i], "_");
        mapData.Type = OdsoFieldMappingType.Column;
        doc2.MailMergeSettings.Odso.FieldMapDatas.Add(mapData);
    }
}

doc2.MailMergeSettings = mms;
doc2.Save(@"D:\Downloads\AsposeMailMerge\Template_Actual_AW178.docx", SaveFormat.Docx);