Excel Tips for Sage 100 Data Import Using Visual Integrator

If you are importing data into Sage 100 ERP using Visual Integrator, these excel tips can help make the process a little easier.

TIP#1 shows you how to change excel column headings from letters to numbers.

TIP#2 explains how to convert numbers to zero-filled text fields.

This article assumes you have a strong working knowledge of Microsoft Excel. If not, contact the team at RKL eSolutions for assistance.

TIP#1:   Change Excel Column Headers From Letters to Numbers

When mapping the fields in your source data to the Sage 100 table, you are asked for the column number, not letter. In excel it is very easy to change your headers from letters to numbers.  You may want to change it back to Letters after your mapping is completed to avoid confusion.

Go to Excel Options > Formulas > Check the option "R1C1 reference style"

Click images for larger screenshot ...

TIP #2: Convert column to text with a specific format to bring back leading zeroes

If your source data file contains fields that are numbers and have a leading zero, Excel automatically drops the leading zeroes.

For example: Item numbers, Zip codes, Terms codes, Divisions.

A method to maintain the leading zeroes is to format the cell as text. The AR Division number, for example, is a 2 digit field that often has a Leading Zero. If you typed in your division number or extracted it from another system, it may appear as a single numeric digit.

In excel, we can create a formula to convert a column to text and add the leading zeroes as needed. The formula can then be copied and pasted as values.

Steps:

1. Insert 2 excel columns next to the column you want to format.
2. Create a TEXT formula in one of the empty columns. For example to convert 1 to '01, the formula is =TEXT(A2, "00"), where A2 = the cell you want to reformat. For a 5 digit zip code, the formula would be =TEXT(A2, "00000")
3. Copy the formula down that column
4. Highlight entire column and select Copy
5. Go to 2nd empty column that was inserted and select Paste Special. Select to paste as value (notice green flag in cell indicating column is stored as Text)
6. Once you have confirmed the data is correct, you can delete the original column and the formula column.
7. After saving file as .csv, you may want to open in word pad to validate your data looks correct before importing.

And there you have it! Now you can use Microsoft Excel tips to make the data importing process a little easier.

If you need help, just contact us for guidance and we'll follow up.

Contact RKL eSolutions

Related Posts

Working With User-Defined Fields in Sage 100 ERP
Sage 100 ERP 2014 Guides & Resources