Difference between revisions of "Creating mail merge file"

From Catalyst
Jump to: navigation, search
 
(8 intermediate revisions by 3 users not shown)
Line 1: Line 1:
 
== Summary ==
 
== Summary ==
−
It is possible for Platinum to export Customer and Supplier names and addresses for use with any good database, spreadsheet or word processor. This article explains how to extract these names, and load them into one particular spreadsheet.  
+
This article explains how to extract customer and supplier names, and load them into one particular spreadsheet.
  
−
== Creating the File ==
+
== More Information ==
−
=== Customers ===
+
It is possible for Platinum to export Customer and Supplier names and addresses for use with any good database, spreadsheet or word processor.
 +
 
 +
=== Creating the File ===
 +
==== Customers ====
 
Go to {{Menu|{{SL}}|Customer File Maintenance}}.
 
Go to {{Menu|{{SL}}|Customer File Maintenance}}.
 
* Click {{Button|Create Mailmerge File}}.
 
* Click {{Button|Create Mailmerge File}}.
 
* This will create a file called {{FilePath|SXMERGE.DAT}} stored in {{FilePath|<Platinum Installation Drive>sx\Sxco??}}. It will overwrite any existing file.
 
* This will create a file called {{FilePath|SXMERGE.DAT}} stored in {{FilePath|<Platinum Installation Drive>sx\Sxco??}}. It will overwrite any existing file.
  
−
=== Suppliers ===
+
==== Suppliers ====
 
Go to {{Menu|{{PL}}|Supplier File Maintenance}}.
 
Go to {{Menu|{{PL}}|Supplier File Maintenance}}.
 
* Click {{Button|Create Mailmerge File}}.
 
* Click {{Button|Create Mailmerge File}}.
Line 15: Line 18:
 
From here on in the document, where you see {{SL}}, this should be read as {{PL}} for suppliers. Where you see "Customer File Maintenance", this should be read as "Supplier File Maintenance" for suppliers.
 
From here on in the document, where you see {{SL}}, this should be read as {{PL}} for suppliers. Where you see "Customer File Maintenance", this should be read as "Supplier File Maintenance" for suppliers.
  
−
== Filtering the output ==
+
=== Filtering the output ===
 
If you want to restrict the people in the output file, this is done using the range screen within "Customer File Maintenance".
 
If you want to restrict the people in the output file, this is done using the range screen within "Customer File Maintenance".
 
Go to {{Menu|{{SL}}|Customer File Maintenance|Alter Range of Reports}}.
 
Go to {{Menu|{{SL}}|Customer File Maintenance|Alter Range of Reports}}.
Line 32: Line 35:
 
* Choose your filters, then {{KeyPress|ESC}} from this screen and then choose {{Button|Create Mailmerge File}}.
 
* Choose your filters, then {{KeyPress|ESC}} from this screen and then choose {{Button|Create Mailmerge File}}.
  
−
== Opening the File in Excel ==
+
=== Opening the File in Excel ===
 
* Open Excel.
 
* Open Excel.
−
* Go to "File" -> "Open".
+
* Go to {{Menu|File|Open}}.
 
* Browse to {{FilePath|<Platinum Installation Drive>sx\Sxco??}}.
 
* Browse to {{FilePath|<Platinum Installation Drive>sx\Sxco??}}.
 
* Change "Files of Type" to "All Files".
 
* Change "Files of Type" to "All Files".
Line 40: Line 43:
 
** This will open the "Text Import Wizard Step 1 of 3" box.
 
** This will open the "Text Import Wizard Step 1 of 3" box.
 
* Select "Delimited" and click "Next".
 
* Select "Delimited" and click "Next".
−
{{Tip|text=At this point you will see the the data split into the appropriate columns in the preview section in the lower part of the window.}}  
+
{{Tip|text=At this point you will see the the data split into the appropriate columns in the preview section in the lower part of the window.}}
 
* Untick "Tab" and tick "Comma" by left clicking and click "Next".
 
* Untick "Tab" and tick "Comma" by left clicking and click "Next".
−
* Highlight each field in turn by clicking each column in the preview section in the lower part of the window and make sure each field is marked as "Text" in the "Column Data Format" section.  
+
* Highlight each field in turn by clicking each column in the preview section in the lower part of the window and make sure each field is marked as "Text" in the "Column Data Format" section.
 
* Click "Finish".
 
* Click "Finish".
  
 
This will show the data within Excel split into the appropriate columns, now go to {{Menu|File|Save As}} and save with an appropriate name and change the "Save As File Type" to "Microsoft Office Excel Workbook (.XLS) and save to a location of your choice. You can then use this file with a word processor to merge this data onto a form letter.
 
This will show the data within Excel split into the appropriate columns, now go to {{Menu|File|Save As}} and save with an appropriate name and change the "Save As File Type" to "Microsoft Office Excel Workbook (.XLS) and save to a location of your choice. You can then use this file with a word processor to merge this data onto a form letter.
  
−
== See Also ==
+
== Creating a CSV file ==
−
* [[How do I use the Retention Centre?]]
+
The alternative to the mail merge file is to create a csv file.
−
* [[How do I Edit Vehicle Sales Mailshot Letters?]]
+
 
−
* [[How do I Edit Workshop Mailshot Letters?]]
+
Go to {{Menu|{{SL}}/{{PL}}|Sales/Purchase Reports|Accounts Report Generator}}.
−
* [[How do I Edit Sales Prospecting Mailshot Letters?]]
+
* At {{DataPrompt|Filter on}} enter {{DataValue|Export}}
 +
* Click {{Button|Start}}.
 +
* Select either {{DataValue|Export Customers}} or {{DataValue|Export Suppliers}}.
 +
* Click {{Button|Start}}.
 +
 
 +
The file will open automatically for you.
 +
 
 +
== See also ==
 +
* [[Getting started - Retention Centre]]
 +
* [[How do I edit Sales Prospecting mailshot letters?]]
  
−
{{KB_Tags|Account, Customer, supplier, export, CSV, comma, separated, file, mailing, merge}}{{KB_SL}}{{KB_PL}}
+
{{KB_Tags|account, customer, supplier, export, csv, comma, separated, file, mailing, merge}}
 +
{{KB_PL}}{{KB_SL}}

Latest revision as of 18:14, 30 August 2019

Summary

This article explains how to extract customer and supplier names, and load them into one particular spreadsheet.

More Information

It is possible for Platinum to export Customer and Supplier names and addresses for use with any good database, spreadsheet or word processor.

Creating the File

Customers

Go to " Sales Ledger Customer File Maintenance".

  • Click [Create Mailmerge File].
  • This will create a file called 'SXMERGE.DAT' stored in '<Platinum Installation Drive>sx\Sxco??'. It will overwrite any existing file.

Suppliers

Go to " Purchase Ledger Supplier File Maintenance".

  • Click [Create Mailmerge File].
  • This will create the same file as above, and overwrite any existing file.

From here on in the document, where you see Sales Ledger, this should be read as Purchase Ledger for suppliers. Where you see "Customer File Maintenance", this should be read as "Supplier File Maintenance" for suppliers.

Filtering the output

If you want to restrict the people in the output file, this is done using the range screen within "Customer File Maintenance". Go to " Sales Ledger Customer File Maintenance Alter Range of Reports".

Here you will see all the things you can filter your output by. The list is as follows:

  • Account Code - (From and To)
  • Balance Range - (From and To)
  • Period Turnover Range - (From and To)
  • Credit Limit Range - (From and To)
  • Discount Type - (From and To)
  • Discount Type Range - (From and To)
  • Account Group - (From and To)
  • Reference - (From and To)
  • Account Analysis Codes - (First Three Positions)
  • Choose your filters, then <Esc> from this screen and then choose [Create Mailmerge File].

Opening the File in Excel

  • Open Excel.
  • Go to " File Open".
  • Browse to '<Platinum Installation Drive>sx\Sxco??'.
  • Change "Files of Type" to "All Files".
  • Left Click "SXMERGE.DAT".
    • This will open the "Text Import Wizard Step 1 of 3" box.
  • Select "Delimited" and click "Next".
At this point you will see the the data split into the appropriate columns in the preview section in the lower part of the window.
  • Untick "Tab" and tick "Comma" by left clicking and click "Next".
  • Highlight each field in turn by clicking each column in the preview section in the lower part of the window and make sure each field is marked as "Text" in the "Column Data Format" section.
  • Click "Finish".

This will show the data within Excel split into the appropriate columns, now go to " File Save As" and save with an appropriate name and change the "Save As File Type" to "Microsoft Office Excel Workbook (.XLS) and save to a location of your choice. You can then use this file with a word processor to merge this data onto a form letter.

Creating a CSV file

The alternative to the mail merge file is to create a csv file.

Go to " Sales Ledger/Purchase Ledger Sales/Purchase Reports Accounts Report Generator".

  • At "Filter on" enter 'Export'
  • Click [Start].
  • Select either 'Export Customers' or 'Export Suppliers'.
  • Click [Start].

The file will open automatically for you.

See also


Feedback
Thank you for using our Knowledge Base, we value your feedback. Did you find this article useful? 'Yes' or 'No'
Keywords AND Misspellings
account, customer, supplier, export, csv, comma, separated, file, mailing, merge
Retrieved from "https://www.catalyst-uk-support.com/kb/index.php?title=Creating_mail_merge_file&oldid=40474"