Sage 50: Editing Sage's Customers/Products Analysis Fields in Excel?

Sage 50:

Setting up custom analysis fields and product categories inside Sage can be a time-consuming process. 

Exporting your Sage data out as a CSV and applying your fields and categories within Excel is a much faster method.

In order to reimport the excel CSV file, you will need to apply the attached reimport script which aligns with Sage. 

If you need any assistance with this process please do not hesitate to get in touch with our support department at [e-mail] or give us a call at 03330 112 882

Please backup your Sage data and follow the instructions below carefully:

Step 1 Backing up your Sage data:

1. Make sure you are logged into the company you wish to backup
2. Click on File, Backup.
3. Select the folder where you want to save the backup
   • Please DO NOT put the file location within the .SAJ folder as this is the folder that the software backs up.
   • The last-used backup file path is saved and becomes the default for the next backup.
4. Click OK to start the backup.

A video on how to backup your data in Sage can be found here

Step 2 Exporting out of Sage:

To export your customer and product Sage data out as a CSV, please follow the following options:

1. In Sage, browse to and select the required report then click Export.

   Products: Products and services > Reports > Product details

   Suppliers: Suppliers > Reports > Supplier details

   Customers: Customers > Reports > Customer details

2. Browse to the folder where you want to save the exported data and enter a file name.
3. In the Save as type drop-down, click Comma Separated Value Files (*.csv) then click Save.
4. Complete the Criteria window as required then click OK.

Step 3 Applying the analysis fields and reimport template in Excel:

In order to apply the desired fields in Sage, please download the required template for the sector you wish to input the analysis fields. 

For additional information on Sage's reimport criteria, please follow the link to an article with further specifications: https://my.sage.co.uk/public/help/askarticle.aspx?articleid=16905

Any information added to the template will overwrite any pre-existing data in Sage. A blank cell will not overwrite what is already inside Sage.

For Customers:

Open the customer export you just downloaded from Sage and in the blank columns K, L & M at the end of the set, allocate your analysis fields. 

Once you have completed allocating your fields, download our Sage customer reimport template here:  

Customer Record FULL template.xls

Copy across the Account Code (Column A) from the exported Sage file into the Account Reference column (Column A) in the reimport template.

Next, copy across the inputted analysis fields from the Sage export into Analysis 1, Analysis 2 & Analysis 3 in columns K, L & M of the reimport template.

Please ensure that the designated analysis fields match up with the correct Account reference on the same row. (If you are using VLOOKUPs, please ensure that all filters are ticked).

Please save the reimport template and skip to Step 3.

For Products:

Open the products export you just downloaded from Sage and in the blank columns G, H & I at the end of the set, allocate your analysis fields. 

Once you have completed allocating your fields, download our Sage products reimport template here:      

Product Record FULL template.xls

Copy across the Product Code (Column A) column from the exported Sage file into the Stock Code column in the reimport template (Column A).

Next, copy across the inputted analysis fields from the Sage export into Web Category A, Web Category B & Web Category C in columns Y, Z & AA of the product reimport template.

Please ensure that the designated analysis fields match up with the correct Stock Code on the same row. (If you are using VLOOKUPs, please ensure that all filters are ticked).

If you wish to apply Product Categories, please add these to the Stock Category Column in Row I of the product reimport category. This step is not required to proceed with applying analysis fields.

Please save the reimport template and skip to Step 3.

For Suppliers:

Open the supplier export you just downloaded from Sage and in the blank columns K, L & M at the end of the set, allocate your analysis fields. 

Once you have completed allocating your fields, download our Sage suppliers reimport template here: 

Supplier Record FULL template.xls

Copy across the Product Code (Column A) column from the exported Sage file into the Stock Code column in the reimport template (Column A).

Next, copy across the inputted analysis fields from the Sage export into Analysis 1, Analysis 2 & Analysis 3 in columns K, L & M of the reimport template.

Please ensure that the designated analysis fields match up with the correct Account reference on the same row. (If you are using VLOOKUPs, please ensure that all filters are ticked).

Please save the reimport template and skip to Step 3.

Step 4 Reimporting back into Sage:

In order to reimport the reimport templates back into Sage, log back into the company you exported the original Sage file out of. Next, go File > Import and follow the prompts on the screen.

Under Data Type, please ensure that the import matches the data type (i.e matching customer reimport template with Customer Record data type).

Next, please find the reimport template and selected it as the Data source.

To finalise, confirm Field Mappings and the import.

Congratulations your import is complete! Please check through your analysis fields and categories to ensure the import was a success.