Three Steps to Data Transfer Workbench: Preparing Data for Import

In the last newsletter,??We exported data from an SAP SQL Query and changed it for a mass change. I picked an example of preparing for MRP implementation. With the data changed, This time I'll show you how to prepare your data for import in DTW. Preparation is the most critical step to get right. Whether you are a channel partner, consultant, or user, running DTW yourself, or having someone else do it, getting the preparation work done right will save you a lot of time and effort down the road.?

Prelude: Getting good data for tab delimited

Before we go through the steps,??I want to explain what happens in DTW. That will help you later when we talk about some of the precautions you should make with your data. If you just want instructions you can skip to the next part, but it won't make as much sense.?

DTW uses tab delimted text. For simplicity, let's look at three columns in my list, code, description, and sales item. Each row has an identifier or primary key, the code. There's also a A text description and a Y or N if it is a sales item. Each of those makes a column. In Excel, we see this:?Now suppose you only have basic keyboard characters to work with. Since 1963, this is the ASCII character set, later to be extended to the ANSI character set. ?Here's a question: how do you make a line of text to correspond to a row in a database table??

One answer is to separate the columns with commas.?

A00001, J.B. Officeprint 1420, Y

A00002, J.B. Officeprint 1111, Y
        

This can have errors. For example what if I had a row like this one??

P1001,PC - 8x core,DDR 32GB, 2TB HDD, Y        

?We only want three columns, but the extra two commas in the description mess up the reading of this. The system thinks there are five, not three columns.?How do we designate three columns? By using quote marks for all strings.?

"A00001", "J.B. Officeprint 1420", "Y"

"A00002","J.B. Officeprint 1111", "Y"

"P10001", "PC - 8x core,?DDR 32GB,?2TB HDD","Y"
        

If we had numbers, say average prices, they would remain outside of quotes, and be only digits and decimal points, avoiding the comma thousand separator.?

"A00001", "J.B. Officeprint 1420", "Y", 125.00

"A00002"," J.B. Officeprint 1111", "Y", 240.95

"P10001", "PC - 8x core,?DDR 32GB,?2TB HDD","Y", 1495.95
        

This is the basic comma separated value you'll find as .csv files. Some CSV files go one step more and add a header to explain the columns

"Code (ItemCode)", "Description (ItemName)", "Sales Item(SellItem)", "price(AvgPrice)"

"A00001", "J.B. Officeprint 1420", "Y", 125.00

"A00002"," J.B. Officeprint 1111", "Y", 240.95

"P10001", "PC - 8x core,?DDR 32GB,?2TB HDD","Y", 1495.95
        

Now you don’t have to use a comma. Other delimiters besides commas started to show up. One common one is a tab, to create the tab-separated value

"Code (ItemCode)"????????"Description (ItemName)"????????"Sales Item(SellItem)"????" price(AvgPrice)"

"A00001"??????????"J.B. Officeprint 1420"??"Y"????????125.00

"A00002"??????????" J.B. Officeprint 1111"?"Y"???????240.95

"P10001"??????????"PC - 8x core,?DDR 32GB,?2TB HDD"???????"Y"???????1495.95
        

The original use of the quotes to handle the comma in the description is not as necessary here. You could drop all the quotes. DTW and similar applications also use the header descriptions to figure out where data is going and check what type it is, so quotes are even less necessary to decide between text, dates, and numbers.?

Code (ItemCode)???????????Description (ItemName)???????????Sales Item(SellItem)??????price(AvgPrice)

A00001?J.B. Officeprint 1420??????Y??????????125.00

A00002?J.B. Officeprint 1111??????Y??????????240.95

P10001?PC - 8x core,?DDR 32GB,?2TB HDD??????????Y??????????1495.95

        

DTW maps the names in the header to columns it knows and then puts the data in the correct row and column.

ItemCode?????????ItemName????????SalesItem??????????AvgStdPrice

A00001?J.B. Officeprint 1420??????Y??????????125.00

A00002?J.B. Officeprint 1111??????Y??????????240.95

P10001?PC - 8x core,?DDR 32GB,?2TB HDD??????????Y??????????1495.95
        

Notice what DTW thinks should be a column name and what we might expect is not the same. You'll notice here the last two columns, where you'd expect the SQL column for OITM, SellItem and AvgPrice has a different name: SalesItem and AvgStdPrice.? To make this easy to work with, you must use the name DTW wants. Getting those column headings correct is the first part of preparing your data.

Open your Data from .txt files

I went into changing the data in detail in the last newsletter, but since we now understand delimited formats, I wanted to go through Excel’s import wizard for tab-delimited formats. I’ll assume you saved a query using Excel export to the .txt format in SAP Business One.?

Open Excel and then open the .txt file.? The text import wizard opens.?

You’ll set the Original data type to Delimited, Start import at row 1 with a Windows Ansi character set. The data has headers so make sure that is checked.??Click Next.?

Step 2 of the wizard asks the character for the delimiter. We use tab. Especially with tabs, you might want to check the treat consecutive delimiters as one. If edit the file as a text file, some users try to align columns with multiple tabs. I usually leave it off since I import and edit it in Excel.?

Always leave the text qualifier as a ", since this is the standard and makes other steps much easier later.??Click Next.?

The last step may save you some work later on. This sets the data type of the columns. Most are fine as General, however, the Item Code should be set to Text, especially if you have numbers for item codes.??In the general setting, leading zeroes will disappear as Excel thinks this is a number. Your item 022 will become 22 otherwise.??Setting this to Text preserves the leading zero, and will align the column correctly.?

Click Finish.??Change your data as described in the last newsletter.?

?

Step 1: Export the template

We must get the header names to be recognized by DTW. Fortunately, there is a list of those names in DTW.??There are templates for importing new data into DTW, and they include the correct column names. That blank template for the table you are importing is a great reference list for our headers.?

Open DTW and Log on to DTW using?the correct company to include your UDF's.?

Go?to templates> Customize Template

Click?Select Object

Select the table you will be importing, in my case Inventory > Item Master Data.

If there are linked tables, you'll see them in the list When you open the folder, select it. For the Item master data I see price lists and others I'd expect.?

Select the correct table by clicking on it. In my case, that's Items.?Right-click on the table and select?Create Template for the Structure.??

?

The system will think a bit then save the template. Add a file name and location.?This will save as an Excel template format(.xlt) with a file name of the table

Close the template maker, and log off of DTW.?

Step 2: Add the headers

In Excel open up the template. SInce this is an old format, you'll get a warning message you can go by. You eventually get this:

Especially on master items and marketing documents, there may be hundreds of columns. If you were migrating data from another system, you'd fill out all these columns to get your data. For changes you are only interested in copying the top header rows. Row 1 has the DTW name for a column, Row 2 has the SAP B1 column name.

If not already open, open up the spresheet of your changes.??Save as a copy?the same filename suffixed with?DTW Ready as an Excel document.

Insert two rows at the top.?

Find a column in the template that matches your data. Copy and paste the two header cells for that column in the template into the data, starting on the top row. You'll repeat for every column. In our example, the first five are easy as they are the first columns of the spreadsheet.??For example I’ll find itemcode in the template, select and copy the two cells

And paste in the blank rows for itemcode.?

?I'll repeat that?process for ItemName, PurchaseItem, SalesItem, and InventoryItem.?

With hundreds of columns, Some columns are harder to find, such as the planning and procurement methods. Here's where having that column name in the SQL header pays off. Using search in the template, find the SAP B1 field Nmae to find the header column in the template

These two are right next to each other so this is an easy case. You can copy and paste them together into your data spreadsheet.?

Step 3: Check for bad characters and data

Quote marks and other nonstandard characters can confuse DTW. Search the file for illegal characters and delete them. Most importantly delete or change any quote marks, since both Excel and DTW use quote marks as a signal of text column. The most common error happens when someone has used quote marks as an abbreviation for inches.???For example, I have this 24-inch monitor

I would replace with this.?

Most often this happens in the description column. Often, I won’t do this step to the description column at all. Instead, I'll delete the column as described in step 4.?

Check your primary key column. If you used numbers in a text field, see if leading zeroes were lost as I discussed earlier, and replace the zeroes. For example,??22 in your spreadsheet becomes 022 or 0022. Replace those missing zeroes. ?

Step 4: Delete unnecessary information

There’s some parts of our spreadsheet DTW will get confused on, or we used for our own reference. Keeping this file with the least number of columns and the smallest size speeds up the process.?

The third row was for our benefit while making changes. Delete the third?row with the verbose header description.?

Delete any columns you are not going to use. Very often the Description column is one to remove. I’m leaving it in to show you errors you can get in DTW, but I guarantee that if any column will give you grief during import, it is Description and Remarks columns. If you are not mass changing or adding the data in these columns delete them from the spreadsheet.?

Step 5: Save

Save your work,??if you made these a separate file aready. Otherwise Save a copy. suffixed with DTW Ready.??

Step 7: Export

Now you'll export iSave a copy as tab delimited text?

Step 6: Check the data as text

Open up your saved file in NotePad or another plain text editor like BlueFish to look at the data. I find Excel may add a few more quotes that will throw off DTW.??For example here with item P10001 and P100o2, because of those commas.

Remove the quotes, being careful not to remove tabs along with the quotes. Save the file again.

If youv'e done all this you have a file that's ready to go for Data Transfer workbench. I find getting rid of known errors before I start importing saves me hours of work in later stages. DTW is not fast in what is does, so limiting the amount of data changed speeds up DTW and speeds up getting to a successful change.

You are through one of the most critical parts for using DTW to change your data. In the next newsletter, we'll look at how to use DTW to make the change.

This was a BizONEness Power tip. Subscribe above or go to BizONEness.comfor back issues of power tips, links to SAP Business One videos on LinkedIn Learning, and more helpful videos to best use SAP Business One.