SAPFree worksheetPDF, Excel, Word exportSAP Business OneDiscrete Manufacturing

SAP B1 Item Master Import Template for Data Transfer Workbench (DTW)

Item master data is the first object most SAP Business One projects migrate, and the Data Transfer Workbench (DTW) is the standard tool. This worksheet gives you a clean place to stage and check items before you map them into the DTW template that ships with your version.

Download:

ItemCode	ItemName	ItemsGroupCode / group name	Item type	Inventory item	Sales item	Purchase item	Inventory UoM	Managed by	Default warehouse	Std or last cost	Opening qty	Opening value
												$4,380
												$7,560
												$900
												$0
												$3,840

Total opening value

$16,680

Item count

Column names follow the OITM fields people load most. The Data Transfer Workbench template files shipped with your B1 version are the authority on exact column headers, so copy values into those templates and keep the two header rows intact. Test in a copy company database first.

Migrating items from another ERP?

We can help script the extract, clean-up and validation for your B1 item load.

Optional. The tool and every export work without this. No spam.

How to use this worksheet

Paste or type your items into the table, one row per item. Use the select columns so flags are Y or N and not free text. Review the opening value column to catch an obvious unit cost typo, such as a cost entered per thousand instead of per piece. Then export to Excel or CSV and copy columns into the DTW Items template. Do not load stock quantities onto the item record. Load opening stock with an Inventory Opening Balance or Goods Receipt so it posts to the right accounts and warehouse.

Clean item codes first: trim spaces, fix case and remove duplicates
Build the item group list and warehouse codes before loading items
Load a small batch of 20 items as a pilot

How DTW works with items

DTW reads tab delimited text files saved from Excel templates. Each business object has a template, and Items has a header row with field descriptions and a second row with the technical field names. You map your source file to those fields in a wizard and run the import against a company database. Because DTW writes through the DI API, it applies B1 validations: a bad group code, duplicate item code or unknown warehouse stops the row with an error log. Check the log, fix the source and re-run only failed rows.

Fields that cause the most rework

Items that look fine in Excel fail or load wrongly when a few fields are inconsistent. Settle them with the business before the first load, because changing a management method on an item with transactions is restricted.

Batch or serial management: set it before any stock exists for the item
Valuation method: standard, moving average or FIFO is a decision for finance
Item group: its default accounts drive posting, so wrong grouping gives wrong ledgers
UoM and UoM groups: package sizes and purchasing units need a UoM group
Default warehouse and preferred vendor: needed for MRP recommendations later

After the load

Run a count check: items in the file against rows in the Item Master Data list. Run a sample check of ten items against the source, reading every field. Then verify that opening stock posted by item and warehouse and that the journal entry hit the inventory and opening balance accounts you expect. Keep the DTW log, the final source file and the sign-off together. These are the evidence an auditor or the business owner will ask for.

Frequently Asked Questions

What is DTW in SAP Business One?

DTW is the Data Transfer Workbench, a tool shipped with B1 that imports and updates master data and documents from delimited text files. It uses the DI API, so it applies the same validations as the screens. It suits one-time migrations and bulk updates.

Can I use this file directly in DTW?

Not unchanged. DTW uses its own template files with specific header rows. Use this worksheet to stage and check data, then copy the columns into the template for your B1 version and keep its two header rows exactly as supplied.

How do I load opening stock?

Use an inventory opening balance or goods receipt document, loaded by DTW or entered in B1, so the posting reaches the correct accounts and warehouses. Putting quantities on the item master is not the standard method.

Should I update existing items with DTW?

Yes, DTW supports updates keyed by item code, which is useful for bulk changes to groups or default warehouses. Always test on a copy of the database, since a wrong mapping can overwrite data on many items at once.

If you are moving item data from SyteLine, Baan or another ERP into B1, we can help script the extraction and validation. Tell us the source system and rough item count.

Talk to an Expert Explore More Free Tools

Related Tools

SAP

More SAP tools

See all

Back to All Tools