Appendix: Example spreadsheet import

Supplier import

This example demonstrates how to import supplier data on a spreadsheet into Sage X3.

Export an associated Sage X3 import template to a spreadsheet application

Open: Setup > Usage > Imports / Exports > Import/export templates | Fields

  1. Open the BPS - Suppliers template.
  2. Click Excel export from the Actions icon to export the associated fields.
  3. Click Export.

    4. An icon for your default spreadsheet application is displayed. For this example we are using Microsoft Excel.

  4. Click the Microsoft Excel icon.

Prepare a new spreadsheet

Open: Microsoft Excel

  1. Open your spreadsheet.


  2. Select the cells to be copied.

    3. For our example, we only want the records in the first four columns (Indicator, Table, Field and Description).

  3. Copy the selected cells to the clipboard.
  4. Open a new blank spreadsheet. Select Paste Special. Select the Transpose check box.

    5. Leave the default Paste All and Operation None check boxes selected.

  5. You will notice that the pasted columns now run horizontally.

  6. Highlight the cells where the first line (the Indicator row) contains the letter B. Apply a Background Color.
  7. Repeat step 6 for all cells where the first line (the Indicator row) contains the letter A.
  8. Highlight column A and Insert a new column.
  9. In cell A1, type H.

    10. Reminder: Header lineH is mandatory in the first cell (the first line of the first column).

  10. In cell A2, and in all subsequent rows in column A, type C.

    11. Reminder: Comment linesC lines are ignored by the Single line files conversion function (AIFIMPGENFIC).

  11. Apply the column B formatting to the cells in column A.

  12. Delete column B.
  13. Leave the new cell B1 set to the letter B. Delete the letter B from all other cells in the first row.

  14. Leave the first occurrence of the letter A in the first row. Delete the letter A from all other cells in the first row.

  15. Repeat for the letter R in the first row.
  16. Duplicate all cells in all rows of section A from one to N times, inserting each duplicated block before the first column of section R.
  17. Duplicate all cells in all rows of section R from one to N times.
    18. There are no restrictions on the number of duplicated sections (section A or section R).
  18. In the final cell of the first row, type END.

    19. This will be the last cell in the first row of the last section R.

    Reminder: ENDThe last column must contain the value END.

    Sage advises that you set a background color for the final cell (END).

Populate the new spreadsheet

Open: Microsoft Excel

Open: Usage > Imports / exports > Exports

  1. Using the Exports function (GEXPOBJ), select the BPS template (Suppliers) as the Template to use.
  2. Range block, Supplier field. Set the Start and End fields to the same supplier code.
    3. You only need to export a single supplier.
  3. Open Microsoft Excel and use the Text Import Wizard to open the export file.
  4. Copy the lines of type B, A and R as required.

    5. Our example has only one supplier.
  5. Populate the lines for each supplier.
    6. H B      
    C BPSUPPLIER BPSUPPLIER BPSUPPLIER BPARTNER
    C / BSGCOD BPSNUM BPRNAM
    C   Category Supplier Company name
      B NEG FR001 DISTRIBUTION SUPPLIER
      B NEG TST001