Spire.Doc is a professional Word .NET library specifically designed for developers to create, read, write, convert and print Word document files. Get free and professional technical support for Spire.Doc for .NET, Java, Android, C++, Python.

Mon Nov 18, 2019 1:21 pm

Hello,

We have a lot of clients having Mail Merge templates with a lot of "normalized" merge fields. They must:

Contain only letters, numbers, and the underscore (_) character
Not exceed 40 characters

We are not aware of all the limitations but have to "normalize" the columns in the data source table to map them to the template's merge fields. Currently we use Word object model and set the data table as a data source of the template. Something like

document.MailMerge.OpenDataSource(...)

then Word "normalize" the fields and they can be returned fom document.MailMerge.DataSource.DataFields

So far, so good but this is extremely slow and we want to write our algorithm for "normalizing". Do you have any similar functionality or know-how of any other limitations?

Regards,
Velislav

profiler007
 
Posts: 72
Joined: Wed Nov 13, 2019 11:32 am

Tue Nov 19, 2019 9:56 am

Hi,

Thanks for your inquiry.
Do you want to format the column name of your data source to make it be consistent with the name of the merge field in your template document? Or do you want to modify the data format in your data source table to the "normalized" format(Contain only letters, numbers, and the underscore (_) character. Not exceed 40 characters)?

To help us investigate your issue accurately, could you please offer us the following information?
1. A sample template file.
2. Your input data file with "normalized" format you desired.

You could upload them here or send us(support@e-iceblue.com) via email.

Best wishes,
Amber
E-iceblue support team
User avatar

Amber.Gu
 
Posts: 525
Joined: Tue Jun 04, 2019 3:16 am

Tue Nov 19, 2019 4:10 pm

Hi,

Since I cannot modify the templates, I want to "normalize" the column names in the data source (data table).

I tried to reverse-engineer Word's algorithms but this is more complicated than I expected. They have very strange algorithm for "normalizing":

- All spaces are replaced by "_".

Fund_GL integration -> Fund_GL_integration

- All non-letter and non-digit symbols except "_" are removed and the length is reduced to 40 symbols

Contact_Totalremainingcommitments(allfunds)EUR -> Contact_Totalremainingcommitmentsallfund (40 characters)

- Sometimes the length is reduced to 39 symbols (!!!) and I don't know why...

InvestorAccount_Investorcategory(Investor) -> InvestorAccount_InvestorcategoryInvesto (39 characters)
InvestorAccount_OMAMreporting-excludeaccount -> InvestorAccount_OMAMreportingexcludeacc (39 characters)

Regards,
Velislav

profiler007
 
Posts: 72
Joined: Wed Nov 13, 2019 11:32 am

Wed Nov 20, 2019 11:44 am

Hi,

Thanks for your reply.
Sorry that there is no direct way to alter the column name of the data source while merging the field using our products. But you could alter the column name of your data source to make it be consistent with the name of the merge field in your template document first, then fill the data from the source into the field in your template document using MailMerge function with Spire.Doc(now the latest version is Spire.Doc Pack(hot fix) Version:7.11.1). I have attached my input and output files here, please check them. Below is the code for you.

Code: Select all
            //Connect the data table.
            OleDbConnection connection = new OleDbConnection();
            connection.ConnectionString = @"Provider=""Microsoft.Jet.OLEDB.4.0"";Data Source=""demo.mdb"";User Id=;Password=";
            OleDbCommand command = new OleDbCommand();
            command.CommandText
                    = " SELECT * "
                    + " FROM parts";
            DataSet dataSet = new System.Data.DataSet();
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command.CommandText, connection);
            dataAdapter.Fill(dataSet, "parts");

            //Alter name of the column “Description” to “New_Column_1” in the table “parts”.
            DataTable dt = dataSet.Tables["parts"];
            dt.Columns["Description"].ColumnName = "New_Column_1";

            //Fill the data from the source into the field in your template document.
            Document document = new Document();
            document.LoadFromFile(@"……\test1.doc", FileFormat.Doc);
            document.MailMerge. ExecuteWidthRegion (dt);           
            document.SaveToFile("result.doc", Spire.Doc.FileFormat.Doc);


Best wishes,
Amber
E-iceblue support team
User avatar

Amber.Gu
 
Posts: 525
Joined: Tue Jun 04, 2019 3:16 am

Wed Nov 20, 2019 4:23 pm

Hi,

The hardest part is to "alter the column name of the data source to make it be consistent with the name of the merge field in the template document first". I asked if you have implemented the algorithm of "normalizing" (reduce the length, replace some characters etc.)...

Regards,

profiler007
 
Posts: 72
Joined: Wed Nov 13, 2019 11:32 am

Thu Nov 21, 2019 8:45 am

Hi,

Thanks for your reply.
Sorry that we don't have the algorithm of "normalizing" as you said while altering the column name of your data source.

Best wishes,
Amber
E-iceblue support team
User avatar

Amber.Gu
 
Posts: 525
Joined: Tue Jun 04, 2019 3:16 am

Return to Spire.Doc