Data Transfer Workbench for

SAP Business One

Data Transfer Workbench for SAP Business One

Purpose

SAP Business One implementations require you to move data from the legacy system into the

new SAP Business One system. Business data (such as Customers, Vendors, and Products)

must be available in the new system before going live.

The Data Transfer Workbench for SAP Business One assists you in your implementation

project to easily migrate legacy data into your SAP Business One system.

Features

The data migration includes different tasks:

  • Extracting the data files from your legacy system
  • Cleaning the data and converting the data
  • Importing the data into SAP Business One
  • Importing the updated data into SAP Business One
  • Checking the results of the import

The Data Transfer Workbench offers an easy-to-use wizard that imports data into SAP

Business One. To simplify the preparation of the data for import, SAP provides predefined

data file templates.

The Data Transfer Workbench records all import activities so that you can track the Data

Migration process. Additionally, data records that failed during the import process will be put

into a separate file that, after data correction, can be used for importing the failed data

  1. records.

System Requirements

The Data Transfer Workbench can be used together with SAP Business One 6.01 or higher. It

can be installed on a workstation. The minimal hardware and software requirements on the

workstation are:

  • Windows 98, Windows 2000, Windows XP, or Windows NT
  • Pentium 233 MHz (recommended: Pentium 300 MHz or higher)
  • 128 MB
  • 24 bit colors
  • 400 MB free disk space

Procedure

  1. 1. Open the Data Migration directory.
  2. 2. Double-click the Data Transfer Workbench.exe Setup program.
  3. 3. Choose Next to start the installation program or Cancel to exit the installation.
  4. 4. Choose Next to confirm the installation folder. To select a different folder, choose

Browse, select the folder and then choose Next.

  1. 5. Choose Next to confirm the default program folder.
  2. 6. On the screen Start Copying Files, review your settings and choose Next to start the
  3. installation.
  4. 7. To complete the installation, choose Finish.

For the Data Transfer Workbench for SAP Business One installation, the ODBC

setting must be set up.

Uninstalling the Data Transfer Workbench

Procedure

  1. 1. Choose Start Settings Control Panel Add/Remove Programs (/Software).
  2. 2. Select Data Transfer Workbench for SAP Business One.
  3. 3. Choose Change/Remove.
  4. 4. Choose OK to confirm the message or choose Cancel to exit without removing the
  5. program.

Chart of Accounts

The Chart of Accounts contains G/L accounts and structure elements (titles) as well. For the

structure elements only the columns G/L Account Code, Account Name and Code of the

Parent Account or Level are relevant

The corresponding template to prepare the data file to be imported is ChartOfAccounts.xlt.

The data file has the following structure:

Column Field Name Mandatory Comment Related

Table

Default

Value

1 G/L Account

Code

Yes Char(15)

2 Title / Active

Account

Yes [X=Structure

Element (Title),

blank=postable G/L

account]

3 Account Name Yes Char(100)

4 External Code

5 Alternative

Account Name

Char(100)

6 Currency Yes [## = Multi-

Currency]

Char(3)

7 Level Can be a value

between 2 to 5.

2

8 Code of the

Parent Account

Yes If empty it will be

derived from the

  1. Level. See also the

note below.

Char(15)

9 Cash Account Yes [1=Yes, 0=No]

10 Account Type Yes [0=Revenues,

1=Expenses,

2=Other]

11 Confidential [1=Yes, 0=No]

12 Reconciliation

Account

[1=Yes, 0=No]

SAP Online Help 6/4/2003

Data Transfer Workbench for SAP Business One 14

Column Field Name Mandatory Comment Related

Table

Default

Value

13 Indexed [1=Yes, 0=No]

14 Revaluation

(Currency)

[1=Yes, 0=No]

15 Code for

Exporting

Char(8)

16 Project Char(8) OPRJ

17 Details Char(254)

18 Relevant to

Budget

[1=Yes, 0=No]

19 Rate Conversion [1=Yes, 0=No]

The Code of the Parent Account field is mandatory to create a new G/L account.

Business Partners

The corresponding templates to prepare the data files to be imported are BusinessPartners.xlt

and BusinessPartners_ContactEmployees.xlt

The data files has the following structures:

  1. BusinessPartners.xlt

Column Field Name Mandatory Comment Related

Table

Default

Value

1 Linetype Yes Only “H” is allowed,

which means Header

2 RecordKey Yes Duplicated key is not

  1. allowed.

Integer(4)

3 Type Yes [0=Customer,

1=Supplier, 2=Lid

(Interested party)]

0

4 Code Yes The key is unique.

Char(15)

5 Name Char(100)

6 Foreign Name Char(100)

7 Group SmallInt(2) OCRG

8 Currency Char(3)

9 Federal Tax ID

10 Tel 1 Char(20)

11 Tel 2 Char(20)

12 Mobile Ph. Char(50)

13 Fax Char(20)

14 E-Mail Char(100)

SAP Online Help 6/4/2003

Data Transfer Workbench for SAP Business One 15

Column Field Name Mandatory Comment Related

Table

Default

Value

15 Contact Person Char(90)

16 Remarks Char(100)

17 Sales Empl. Define Sales Employee

code in advanced

SmallInt(2)

OSLP -1

18 Commission

Group

SmallInt(2) OCOG 0

19 Comission % for

Customer

Double(9)

20 Street / P.O Box Char(100)

21 City Char(100)

22 Zip Code Char(20)

23 County Char(100)

24 Country Char(3) OCRY

25 Street / P.O Box

(Mail)

Char(100)

26 City (Mail) Char(100)

27 Zip Code (Mail) Char(20)

28 County (Mail) Char(100)

29 Country (Mail) Char(3) OCRY

30 Payment Terms Define Payment Terms

in advanced

SmallInt(2)

OCTG -1

31 Price List Define Price List in

advanced

OPLN

32 Total Discount

%

Double(9)

33 Credit Limit Double(9)

34 Max.

Commitment

35 Account Char(50)

36 Branch Char(50)

37 Bank Char(30) ODSC -1

38 Average Delay SmallInt(2)

39 IBAN Char(50)

40 Credit Card

Type

SmallInt(2) OCRC

41 Credit Card No. Char(20)

42 Expiration Date Date [YYYY-MM-DD]

43 Debitor Account

SAP Online Help 6/4/2003

Data Transfer Workbench for SAP Business One 16

Column Field Name Mandatory Comment Related

Table

Default

Value

44 VAT Status Char(1) Y

45 VAT Group Char(8)

46 Details (Picture) Char(200)

47 Details

(Freetext)

Char(2147483647)

48 Set Validity Char(1)

49 Valid From

(Date)

Date [YYYY-MM-DD]

50 Valid To (Date) Date [YYYY-MM-DD]

51 Remarks for

Validity

BusinessPartners_ContactEmployees.xlt

Column Field Name Mandatory Comment Related

Table

Default

Value

1 Linetype Yes Only “I” is allowed,

which means Item

2 RecordKey Yes Corrsponding to the

RecordKey of the

main structure

3 Name Yes Char(50)

4 Position Char(90)

5 Address Char(100)

6 Phone1 Char(20)

7 Phone2 Char(20)

8 MobilePhone Char(50)

9 Fax Char(20)

10 E-Mail Char(100)

11 Pager Char(30)

12 Remarks 1 Char(100)

13 Remarks 2 Char(100)

14 Password Char(8)

SAP Online Help 6/4/2003

Data Transfer Workbench for SAP Business One 17

BusinessPartners_Addresses.xlt

Column Field Name Mandatory Comment Related

Table

Default

Value

1 Linetype Yes Only “I” is allowed,

which means Item

2 RecordKey Yes Corresponding to

the RecordKey of

the main structure

3 AddressName Yes Char(50)

4 Street Char(100)

5 Block Char(100)

6 City Char(100)

7 ZipCode Char(20)

8 County Char(100)

9 State Char(3) OCST

10 Country Char(3) OCRY

11 FederalTaxID Char(32)

Items

The corresponding template to prepare the data file to be imported is Items.xlt.

The data file has the following structure:

Column Field Name Mandatory Comment Related

Table

Default

Value

1 Item Number Yes The key is unique.

Char(20)

2 EAN Code International Item

Number (EAN) or

barcode

Char(16)

3 Description Char(100)

4 Desc. In Foreign Char(100)

5 Item Grouping SmallInt(2) OITB 100

6 Purchased Item Char(1)

Yes = 1 No = 0

1

7 Sales Item Char(1)

Yes = 1 No = 0

1

8 WH Item Char(1)

Yes = 1 No = 0

1

9 Fixed Asset Char(1)

Yes = 1 No = 0

0

10 Regular Vendor Char(15) OCRD

SAP Online Help 6/4/2003

Data Transfer Workbench for SAP Business One 18

Column Field Name Mandatory Comment Related

Table

Default

Value

11 Originator Catalog

  1. No.

Char(17)

12 Unit of Meas. for

  1. Purch.

Double(9)

13 Items per purch.

Unit

Double(9)

14 Packaging Unit Double(9)

15 Quantity per

Packaging Unit

Double(9)

16 Customs Group SmallInt(2) OARG -1

17 Tax Liable Char(8)

18 Local Expense

  1. Acc.

Char(15)

19 Length

(PurchaseUnit)

Double(9)

20 Width

(PurchaseUnit)

Double(9)

21 Height

(PurchaseUnit)

Double(9)

22 Volume

(PurchaseUnit)

Double(9)

23 Unit of meas. for

purchase

(PurchaseUnit)

Double(9)

24 Weight

(PurchaseUnit)

Double(9)

25 Unit of Meas. For

Sales

Double(9)

26 Items per Sales

Unit

Double(9)

27 Packaging Unit

Sales

Double(9)

28 Quantity per

Packaging Unit

Double(9)

29 Commission Group SmallInt(2)

30 % Comm. for Item Double(9)

31 Sales Tax

Definition

Char(8) OVTG

32 Loc. Revenues

  1. Acc.

Char(15)

33 Exempt Income

Account

Char(15)

34 Length (SalesUnit) Double(9)

SAP Online Help 6/4/2003

Data Transfer Workbench for SAP Business One 19

Column Field Name Mandatory Comment Related

Table

Default

Value

35 Width (SalesUnit) Double(9)

36 Width Unit of

  1. meas.(SalesUnit)

Char(5)

37 Height (SalesUnit) Double(9)

38 Volume (SalesUnit) Double(9)

39 Unit of meas. for

sales (SalesUnit)

Char(5)

40 Weight (SalesUnit) Double(9)

41 Desired Stock

Level

Double(9)

42 Minimum Stock

Level

Double(9)

43 Manage Serial

Numbers

Char(1)

44 Manage Batch

Numbers

Char(1)

45 Factor1 (Sales) Double(9)

46 Factor2 (Sales) Double(9)

47 Factor3 (Sales) Double(9)

48 Factor4 (Sales) Double(9)

49 Factor1 (Purchase) Double(9)

50 Factor2 (Purchase) Double(9)

51 Factor3 (Purchase) Double(9)

52 Factor4 (Purchase) Double(9)

53 Details (Text) Char(2147483647)

54 Details (Picture) Char(200)

55 (internal field)

(Serial Num)

Char(17)

56 (internal field)

(Tree Type)

Char(1)

57 Valid Char(1)

58 From Date [YYYY-MM-DD]

59 To Date [YYYY-MM-DD]

60 Remarks

SAP Online Help 6/4/2003

Data Transfer Workbench for SAP Business One 20

Items – Prices

The corresponding template to prepare the data file to be imported is Items_Prices.xlt.

The data file has the following structure:

Column Field Name Mandatory Comment Related

Field

Default

Value

1 Item Code Yes Char(20) OITM

2 Price List No. Yes SmallInt(2)

3 Price List

Name

4 Price

(Currency)

Char(3)

5 Price (Amount) Double(9)

Special Prices

The corresponding template to prepare the data file to be imported is Items_SpecialPrices.xlt.

The data file has the following structure:

Column Field Name Mandatory Comment Related

Table

Default

Value

1 Card Code Yes Char(15) OCRD

2 Item Code Yes Char(20) OITM

3 Price List No. SmallInt(2) ITM1

4 Discount in % Double(9)

5 Special Price Double(9)

Bill of Materials

The corresponding templates to prepare the data files to be imported are

ProductTrees_Header.xlt and ProductTrees_Item.xlt

The data file has the following structure:

ProductTrees_Header.xlt

Column Field Name Mandatory Comment Related

Field

Default

Value

1 Linetype Yes Only “H” is allowed,

which means Header

2 RecordKey Yes Duplicated key is not

  1. allowed.

Integer(4)

3 Product No. Yes Char(20) OPRD

4 Product Type [0=Not a tree,

1=Assembly tree,

2=Sales tree,

3=Production tree,

SAP Online Help 6/4/2003

Data Transfer Workbench for SAP Business One 21

Column Field Name Mandatory Comment Related

Field

Default

Value

4=Template tree,

9=Son of sales tree]

5 Quantity Double(9)

ProductTrees_Item.xlt

Column Field Name Mandatory Comment Related

Table

Default

Value

1 Linetype Yes Only “I” is allowed,

which means Item

2 RecordKey Yes Corrsponding to the

RecordKey of the

main structure

3 Item Code Yes Char(20) OITM

4 Quantity Double(9)

5 Warehouse Char(8) OWHS

6 Unit Price

(Amount)

Double(9)

7 Unit Price

(Currency)

Char(3)

Accounting Journal Entries

The corresponding templates to prepare the data files to be imported are

JournalEntries_Header.xlt and JournalEntries_Item.xlt

The data files have the following structures:

JournalEntries_Header.xlt

Column Field Name Mandatory Comment Related

Table

Default

Value

1 Line

RecordKey

Yes Only “H” is allowed,

which means Header

2 RecordKey Yes Duplicated key is not

allowed

3 JdtNum

4 Reference 1 Char(11)

5 Reference 2 Char(11)

6 Details Char(50)

7 Project Char(8) OPRJ

JournalEntries_Item.xlt

Column Field Name Mandatory Comment Related

Table

Default

Value

1 Linetype Yes Only “I” is allowed,

which means Item.

2 RecordKey Yes Corrsponding to the

R dK fth

SAP Online Help 6/4/2003

Data Transfer Workbench for SAP Business One 22

Column Field Name Mandatory Comment Related

Table

Default

Value

RecordKey of the

main structure

3 G/L ACC./BP Char(15) OACT

4 Ref 1 Char(11)

5 Ref 2 Char(11)

6 Debit(FC) Double(9)

7 Credit(FC) Double(9)

8 Credit (FC) /

Debit (FC) OR

Base Amount?

Char(3)

9 Debit Double(9)

10 Credit Double(9)

11 Value Date Date [YYYY-MMDD]

12 Tax Date Date [YYYY-MMDD]

13 Project Char(8) OPRJ

14 Costing Code Char(8) OOCR

15 Offsetting

Account

Char(15)

16 Details Char(50)

Sales and Purchasing Documents

The corresponding templates to prepare the data files to be imported are

Documents_Header.xlt and Documents_Item.xlt

The data files have the following structures:

Documents_Header.xlt

Column Field Name Mandatory Comment Related

Table

Default

Value

1 Linetype Yes Only “H” is

allowed, which

means Header

2 RecordKey Yes Duplicated key is

not allowed

3 Manual Numbering Char(1)

4 Document Number Yes Integer(4)

5 Document Currency Char(3)

6 For Foreign

Currencies

Double(9)

7 Customer/Vendor

Code

Char(15)

OCRD

8 Customer/Vendor

N

Char(100)

SAP Online Help 6/4/2003

Data Transfer Workbench for SAP Business One 23

Column Field Name Mandatory Comment Related

Table

Default

Value

Name

9 Bill To Char(254)

10 Contact Person Integer(4) OCPR

11 Document Date OR

Posting Date

Date [YYYY-MMDD]

12 Valid to Date,

Delivery Date,

Value Date, OR

Due By

Date [YYYY-MMDD]

13 Customer/Vendor

  1. Ref. No.

Char(16)

14 RevisionPo Boolean

0 = No

1 = Yes

15 Remarks Char(254)

16 Journal Remarks Char(50)

17 Sales Employee SmallInt(2) OSLP

18 Payment Terms

Code

SmallInt(2)

OCTG

19 Document Total Double(9)

20 Document Status Char(1)

Documents_Item.xlt

Column Field Name Mandatory Comment Related

Table

Default

Value

1 Linetype Yes [H=Header, I=Item]

Only “I” is allowed in

the structure

2 RecordKey Yes Corrsponding to the

RecordKey of the

main structure

3 Item No. Char(20) OITM

4 Catalog No. Char(14)

5 Item

Description

Char(100)

6 Quantity Double(9) 1

7 Factor 1 Double(9)

8 Factor 2 Double(9)

9 Factor 3 Double(9)

10 Factor 4 Double(9)

11 Price Double(9)

SAP Online Help 6/4/2003

Data Transfer Workbench for SAP Business One 24

Column Field Name Mandatory Comment Related

Table

Default

Value

12 Discount % Double(9)

13 Tax Code

EMEA

Char(8) OSTC

14 Tax Code

US

15 WH Char(8) OWHS

16 Acc. No. Char(15) OACT

17 Costing Code Char(8) OOCR

18 Project Code Char(8) OPRJ

Stock Transfers

The corresponding templates to prepare the data files to be imported are

StockTransfer_Header.xlt and StockTransfer_Items.xlt

The data files have the following structures:

StockTransfer_Header.xlt

Column Field Name Mandatory Comment Related

Table

Default

Value

1 Linetype Yes Only “H” is allowed,

which means

  1. Header.

2 RecordKey Yes Duplicated key is

not allowed

3 Customer/Vendor

Code

Yes [0=Customer,

1=Supplier, 2=Lid]

OCRD

4 Customer/Vendor

Name

Yes Char(100)

5 Address Char(100)

6 Posting Date Date [YYYY-MMDD]

7 Warehouse Code Char(8) OWHS

8 Remarks SmallInt(2)

9 Journal Remarks Char(50)

10 Price List Smallint(2)

11 Sales Employee SmallInt(2)

SAP Online Help 6/4/2003

Data Transfer Workbench for SAP Business One 25

StockTransfer_Items.xlt

Column Field Name Mandatory Comment Related

Table

1 Linetype Yes Only “I” is allowed,

which means Item.

2 RecordKey Yes Correponding to the

RecordKey of the

main structure

3 Item Code Char(20) OITM

4 Item

Description

Char(100)

5 Warehouse

Code

Char(8) OWHS

6 Quantity Double(9)

7 Price Double(9)

Stock Taking

The corresponding templates to prepare the data files to be imported are StockTaking.xlt and

StockTransfer_Items.xlt

The data files have the following structures:

  1. StockTaking.xlt

Column Field Name Mandatory Comment Related

Table

Default

Value

1 Linetype Yes Only “H” is allowed,

which means

  1. Header.

2 RecordKey Yes Duplicated key is

not allowed

3 ItemCode Yes Char(20) OITM

4 WarehouseCode Yes Char(8) OWHS

5 Counted double

SAP Online Help 6/4/2003

Data Transfer Workbench for SAP Business One 26

Contacts

The corresponding templates to prepare the data files to be imported are Contacts_Header.xlt

and Contacts_Attachment.xlt

The data files have the following structures:

Contacts_Header.xlt

Column Field Name Mandatory Comment Related

Table

Default

Value

1 Linetype Yes Only “H” is allowed,

which means Header

2 RecordKey Yes Duplicated key is not

allowed

3 Card Code Yes [0=Customer,

1=Supplier, 2=Lid]

OCRD

4 Contact Person

Code

Yes Integer(4) OCPR

5 Telephone Char(20)

6 Date Date [YYYY-MM-DD]

7 Contact Time Integer(4)

8 Recontact Date Date [YYYY-MM-DD]

9 Closed Contact Char(1)

10 Contact Subject SmallInt(2) OCLS -1

11 Details Char(60)

12 Linked

Document Type

SmallInt(2)

13 Assigned

Document

Number

Char(20)

Contacts_Attachment.xlt

Column Field Name Mandatory Comment Related

Table

Default

Value

1 Linetype Yes Only “I” is allowed,

which means Item

2 RecordKey Yes Correponding to the

RecordKey of the

main structure

3 FileName A file must exist.

Char(254)

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

Leave a Reply