Data Preparation

Frequently, data files require preparation in spreadsheet form before they can be imported. The products being imported must have a Product Number, Description, Product Line, and UPC # in order to be imported. Additionally, this information must be contained in individual fields. For example, if a product description is divided into a size column and a description column, the two must be combined under a single description heading. One of the most common problems with data from a manufacturer is fragmented information that needs to be condensed so that it can be entered into a single field. In this case, you will need to combine or add text to one field for multiple products. To help you achieve this, Microsoft Excel has a built in Concatenate function. This allows you to combine two or more fields, or add repeated text or numbers to an existing field.

 

To help you better understand this function, let's look at an example where this can be used to prepare data. The image below is a spreadsheet of sample data from Charlotte Pipe and Foundry. This data cannot be directly imported into the system because of the way that the manufacturer has divided the UPC # into two segments; the first 6 digits in the column heading, and the last five attached to the appropriate product.

In order to successfully import these products, we need to combine the two segments of the UPC # so that the system reads it correctly. To do this, follow the easy steps below.

 

1.First, insert a new column to hold the concatenated data. To do this, right click on one of the cells in the column, then click Insert... in the drop down menu. Click the circle to the left of Entire Column and then click the OK button. This will insert an empty column to the left of the column that you selected.

 

2.Click on an empty cell in the new column and then click Insert from the main menu bar, and select Function from the drop down menu.

 

3.In the Category drop down menu of the function window, select Text. Then, select Concatenate in the Select a Function window and click OK.

 

4.This will open the concatenate window. In the Text 1 field, enter the text or numbers that you want to add to the existing cells, then press Tab. For this example, we want to enter "611942".

 

5.Then, in the Text 2 field, enter the location of the first spreadsheet cell that the text or numbers need to be added to. You can do this by simply clicking on the cell in the spreadsheet.

 

6.Now click the OK button. This will insert the new combined entry into the blank column that you created, as shown in the example below.

 

7. Now we need to copy this formula so that it can be used to combine the rest of the UPC #'s. To do this, right click on the newly created cell and select Copy from the drop down menu.

 

8. Then select the target blank cells by clicking on the cell below the new UPC # cell. Hold down theShift key and press the Page Down key to select multiple cells. Continue pressing the Page Down key until all of the destination cells have been selected.

 

9. Then, hold down the Ctrl key and press the V key to paste the concatenate formula into the blank cells.

 

10. After pasting the newly combined UPC #, you must make one final change to this column. It must converted from the current state, which contains the formula for the code, to the actual values that will be used in your system. To do this, first click on the column header to select all of the data in the column.

 

11. Then, right-click on the header and select Copy from the drop down menu.

 

12. Now, with the column still selected, right-click again and select Paste Special from the drop down menu.

 

13.In the pop-up window, select Values and then click the OK button. This will paste the actual values, instead of the formula, into the selected cells.

 

14.If you would like to simplify your data, you may now delete the original column of data. This is optional.

 

Remember! When you have completed your changes to the spreadsheet it must be saved as a .csv file. For help importing the edited spreadsheet, return to the Product Import page.


Continue to Import a Product >>