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.
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.
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. |