Importing Mass Changes in DTW

Author's Note: Images may not be showing in the article. If you cannot see the screenshots I have posted this and the rest of the series in the newsletter archive at BizONEness.com.

We've been preparing data for a bulk change in SAP Business one using Data Transfer Workbench in previous newsletters. This week we'll make those changes.?

Before we do, there are cautions. The first should be obvious: you are changing your business data, which requires a meticulous approach. Change the data in a sandbox company first and then verify the data change. You can do as I did here, first testing the entire idea in SBODEMO. I've often used a three-tier approach of creating my change in SBODEMO, trying it there, then doing the test scenario on a sandbox, testing and verfying again. When both pass testing, then change your data in your live production system. There are a few wrinkles here, and I'll address them a little later.?

Especially with data like marketing documents, the data fields are the same, and if you are not careful, you can change the incorrect data. Be cautious in picking the correct data.?

Before you do a live system change, do a backup. I prefer to do DTW work in off-hours. I get better processing time plus a backup from a database that won't change on me from new user entries should something go wrong.?

I'll assume you've done all the steps in the last newsletter. You've done everything to clean your data and have it ready for DTW. The shortlist of those were:?

• Set the headers to the same as the DTW template
• Remove any unnecessary columns. IF you aren't changing it, don't put it in.
• Look for illegal or confusing characters in your text. I prefer to remove all quote marks among these characters.?
• In Excel, check your key has the correct type and is displaying the key correctly.?
• After export from Excel, recheck the text in a text editor that shows invisible characters to make sure your delimiters are correct.?

I'm going to use two files that have errors to show you what an error will look like. Typically you'll be doing a similar process to me, but hopefully, your data is perfectly clean when you run it through DTW, and you get it on the first shot. Be aware that is as likely as winning the lottery.?

Since everyone's system is different, I'll assume you know how to launch DTW. Like we did in the last newsletter, log in to DTW.

Once logged in, click the Import button. The data import wizard appears.?Click next. You'll select your data type. The types listed are pretty self-explanatory

Item data is master data, so I'll select that, then click Next again.?

The next step is Operation type. I'm only showing you DTW for updating or mass changes of existing data here. Any time you need to add new data, as you see in two of the selections, get a professional to do it. You will use the entire template, not our abbreviated one. So always select Update Existing Data and Next.?

We get the Business object selection.?

This example is OITM data, So select the Inventory >Item Master data. Click Next.?

We load the data source file we modified. Again, we used items, so I'll click the ellipsis next to Items.

And select the file you want to use. I'm going to use one with a common error for an example. Click okay, and next.

Your field names appear. You'll see they match perfectly. This matching is what you want, and why you added the header in the Excel file.

You might be able to tell we are in for a whopper on an error right here, But we'll see it shortly. Click Next.

Finally, we hit the error handling step.

Depending on the size of your file, you can make one of these selections.?

• Cancel Import and Perform Rollback When One or More Errors Occur.?This one can be tedious but it is the safest. It fails on a single error but does not change your data until you have no errors. On very large tables, this will save you time since it stops when the first error occurs. However, you have to fix each error one at a time. If you've got to zero errors using one of the other two selections first, this is a good selection.
• Ignore All errors and Process Valid records. Process the ones that are correct but not the ones that have errors.?I often use this one as a first testing one using simulation mode only, but rarely use it for my final production. It can take the longest time of the three, but you will get a report on all the errors.
• Ignore up to 10 errors and process Valid records. This is a compromise of the two and my usual one for final production database changes. Until you hit a limit of errors, usually 10, processing will continue. Over ten errors, the process stops. This give the time advantage of the first option with more to fix errors of the second.

I'm a small file of 106 records. Speed is not as important for me, but finding errors is, so I'll choose the middle one.??

Don't hit next. Your next step is to click Simulation. This analyses it for errors without changing any records.?

DTW runs in simuation mode. We have 26 errors.?Click close.?You get a list of the results for every record. under the display dropdown, select failed. You'll see the list of error messages.?

There are quite a few errors, but many have a common reason: you tried to change a read-only field.?

DTW has an error file it exports to look at this data. If I open it up at olItems\Items.text, I find the problem:?

IN our data, we had the sales item, inventory Item, and purchasing item field. If there are transactions to this inventory item, this becomes a read-only field and cannot be changed. Those columns should have come out of the Excel spreadsheet but didn't.?

For speed reasons, we should not have them in there anyway. Anything but the key you are not changing should not be in your file. Go back to your original excel spreadsheet and remove the columns, and re-export the data.?

I've got a second file where I've done this already.? Close the window and the import wizard. Rerun the import wizard and try the Simulation again withyour corrected data.?

You get a lot less, but different errors

?Again, let's look at the data log.?

We got a few different errors here. With the Invisibles active. You can see Item I00002 has a space instead of a tab. Item I00003 is missing the make or buy, which in this case is buy. z0200 has a lowercase b, and SAP only recognizes a capital B.?Z-03 has an entirely wrong letter Q instead of B or M.?

z-04 is one of those cases if you export your production data and try testing on an old sandbox. The sandbox may not have all the records you exported if users added new records since the sandbox was made. Under the best conditions, update the sandbox just before you start working with DTW. Always reconcile the data from one system to another when you run DTW.

Make those corrections, Go through the import process again and check if all records are successful. If not, you keep repeating this process. The only exception is the records you know are missing due to sandbox and production company discrepancies.?

Once you have cleared all the errors, you can hit next and let DTW change records. The record changing takes longer than the simulations, so you may be waiting a while for it to finish for a big table, possibly more than an hour or two.?

Once again, reconcile the data. You can run the original SQL report and manually check that the data did change as expected.?

If all of that works, you try this on the production company, repeating the steps above to make sure it works fine and then checking the data one more time.?

After all that, you are done. It's a lot of work to get in, but doing all that extra work saves you from doing many manual entries. DTW can be a helpful tool, but it requires a lot of conscious effort to be sure it is used correctly or make a bigger mess than where you started.?

?