Show TOC

Procedure documentationImporting Counting Results from Microsoft Excel

 

You can import your counting results into an inventory counting document from a pre-filled Microsoft Excel file, which has been saved as a TXT file. SAP Business One supports the following TXT formats for import:

  • Tab-delimited

  • Unicode

  • MS-DOS

Note Note

The Macintosh TXT format is not supported.

End of the note.

Note Note

The import function is also available for inventory posting documents and inventory opening balance documents, but the fields available for import are different. For more information, see Fields for Import.

End of the note.

Procedure

  1. Prepare an appropriate import template:

    1. From the SAP Business One Main Menu, choose Start of the navigation path Inventory Next navigation step Inventory Transactions Next navigation step Inventory Counting Transactions Next navigation step Inventory Counting End of the navigation path.

    2. In the Inventory Counting window, define the counting type.

      The fields available to the import template differ depending on your counting type.

    3. Choose the Add Items button and then choose Import Items.

    4. In the Import from Excel window, select relevant fields, depending on which information you want to verify through counting.

    5. Save the template:

      1. Choose the Save As button.

      2. In the Save As Data Import Template window, define the template code and name.

      3. Choose the Save button.

    For more information about the fields, see Fields for Import.

  2. Open Microsoft Excel and create a spreadsheet, based on the template defined in step 1.

    You must define the same fields in the same order as the template.

  3. Enter your counting results into the spreadsheet and then save the spreadsheet in a supported TXT format.

    Note Note

    Before saving the spreadsheet into a TXT file, you must remove the headers, if any, and keep only the counting results. For an example of how to prepare import counting data, see Example of Preparing Import Data.

    End of the note.

  4. Import your counting results into SAP Business One:

    1. Go back to the Inventory Counting window.

    2. Define the counting type and specify the counters.

    3. Choose the Add Items button and then choose Import Items.

    4. In the Import from Excel window, choose the browse button beside Use Data Template and select the template defined in step 1.

    5. Select either of the following import methods:

      • If you want to import all data in the spreadsheet and update existing records, select the Add New Records and Update Existing Records radio button.

      • If you want to import all data in the spreadsheet except for items (in the same storage locations) that already exist in the inventory counting document, select the Add New Records Without Updating Existing Records radio button. The system prevents you from adding the document if any duplicate items exist.

    6. [Optional] To perform a pre-check on your spreadsheet, select the Check Import File for Errors checkbox.

      The system will return any error that is found in the spreadsheet. If you do not select this checkbox and directly import your counting results, the system will notify of all errors after the import completes.

    7. Choose the OK button.

    8. In the Open window, select the TXT file that stores your counting results and choose the Open button.

    The system imports your counting results into the Inventory Counting window.

  5. Make any necessary adjustments to the counting results.

  6. Select the Counted checkbox in each row for which you have completed entering results.

    Note Note

    You can double-click the Counted column header to select all checkboxes in this column, as in the Freeze column. However, double-clicking other columns (for example, Item No.) sorts the values.

    The Counted checkbox is automatically selected in the following scenarios:

    • Single counter: A positive quantity is entered.

    • Multiple counters (individual counters only): Positive quantities are entered for all individual counters.

    • Multiple counters (team counters only): A positive quantity is entered for at least one team counter.

    • Multiple counters (individual counters + team counters): Positive quantities are entered for all individual counters and at least one team counter.

    End of the note.

  7. To save the counting results in SAP Business One, choose the Add button (or the Update button for an existing inventory counting document).

Result

If you feel satisfied with the counting results (for example, differences between counters are small or rare), you can proceed to post the results to adjust your inventory records in the system. For more information, see Posting Counting Results.

More Information

Fields for Import

Example of Preparing Import Data

Inventory Counting

Manually Entering Counting Results

Posting Counting Results

Inventory Counting Window

Printing Inventory Counting Documents