Note that you can click the button (or press F5) to invoke field help at any time from within the job panel itself.

Business Benefits

When creating new product records it may be easier to automatically import multiple records rather than enter each of them manually. This program allows users to import a spreadsheet containing a list of product codes and related attributes. This avoids the need to set these up one at a time in CR.62.

Note: For related documentation and workflows, see CR - Client Related.

Usage

This program imports data from a Comma Separated Value (CSV) file using an already existing product code as a template. It is important that the source spreadsheet (used to create the CSV) is in a format that the program expects. This may mean moving or deleting columns of data as well as some other manipulation within a spreadsheet application such as Excel. The basic steps for setting up and importing CSV data are provided in the sections below.

Preparing Spreadsheet Source

The data sequence left-to-right (Product code appearing in Column A, Product Description 2 in Column B, etc.) is as follows:

The only required data elements are Product Code and Product Description 1.

Data elements not used must have a comma in the file. For example,

Product:12345 Description: Granola bars Unit of Measure: CS Gross Weight: 100 Net Weight: 90 Pallet Config: 36 ....
... would appear in the CSV as : 12345,Granola bars,,CS,,,100,90,,,,,,,36

If you intend to import data that includes either the Generic Code, Sales Group or Unit of Measure code then these codes must already have been set up in the corresponding maintenance programs; i.e., CR.32, CR.05, CR.10.

You can choose to import the individual components of Tie/Tier and Oddtie or else you can import just the Pallet Configuration [Pallet Configuration = (Tie*Tier) + Oddtie]

You can import all the data elements, just the product code by itself or any combination in between.

All other data elements will be copied from the template product that you will have setup in CR.62.

FPTA elements not provided will be copied from the template product that is entered, otherwise the system will use the data in the import file.

The program will check for mandatory fields, non compliant data type, or length existing Unit of measure, generic code or sales group code, duplicate product code.

A report will print after the first pass. This will indicate either that all records pass the validation or that there are issues that must first be resolved.

To create the file itself this can be done in a spreadsheet, then saved as a .csv file. The file can be uploaded to the server using a utility like FTP or WinSCP (for ssh).

The length of the GTIN Code (CR.62 - Field # 76) is variable and dependent on the GTIN Type (CR.62 - Field # 75). For example,

Spreadsheet Examples

Following are some examples showing how the data might appear in the spreadsheet:

Saving the Spreadsheet To CSV (via MS Excel)

1. Prior to saving the spreadsheet to a CSV (.csv) file:
   • Delete any header rows – the first row must begin with data
   • Delete any blank rows after the last product code – these may contain spurious characters
   • Use the “find” command to look for any commas in the data – these may occur in product descriptions. Replace those commas with another character; e.g., " - ".
2. Use the ”Save As” function in Excel and select the CSV (comma delimited) option. Use a filename without embedded blanks and all lowercase characters.
3. Once saved, locate the new .csv file, then open it using Notepad to review the output. Notepad is usually found under Start / All Programs / Accessories in Windows.

Using the data illustrated under the previous Spreadsheet Examples, this is what you should expect to see in the resulting CSV files:

Example 1:

1298624,"Valve 1"" 24X19 W. NICH.",,EA,,,3.527396664,1.6,3.527396664,1.6,375,170,110

1298626,"Valve 1"" 24X20 W. NICH.",,EA,,,4.188783538,1.9,4.188783538,1.9,375,170,110

1298628,"Valve 1"" 24X19.5 W. NICH.",,EA,,,4.766394742,2.162,4.766394742,2.162,375,170,110

1298630,"Valve 1"" 24X20.5 W. NICH.",,EA,,,5.599742203,2.54,5.599742203,2.54,525,170,110

1298632,"Valve 1"" 24X21 W. NICH.",,EA,,,6.258924455,2.839,6.258924455,2.839,525,170,110

Example 2:

1298624,"Valve 1"" 24X19 W. NICH.",,EA

1298626,"Valve 1"" 24X20 W. NICH.",,EA

1298628,"Valve 1"" 24X19.5 W. NICH.",,EA

1298630,"Valve 1"" 24X20.5 W. NICH.",,EA

1298632,"Valve 1"" 24X21 W. NICH.",,EA

Example 3:

1298624,"Valve 1"" 24X19 W. NICH."

1298626,"Valve 1"" 24X20 W. NICH."

1298628,"Valve 1"" 24X19.5 W. NICH."

1298630,"Valve 1"" 24X20.5 W. NICH."

1298632,"Valve 1"" 24X21 W. NICH."

Copying the File to the Server

The file needs to be saved to directory on the MAVES server. This should NOT be the default data directory. Check with your IT department on which directory to use and how best to do the copy. For instance the data path could be /home/import

Common methods for copying files are to:

a) Set up a mapped drive on your PC that is linked to the directory on the server. This way you just save to this drive, and then the file is automatically copied to the server

b) Use a utility such as WinSCP to copy the file. (www.winscp.net).

Importing via CR.70

In CR.70 you specify the client code, the template product and the filename. Note that UNIX is case sensitive so if you saved your file as “Myfile” you will need to type this in exactly because “myfile” will not work.

When you enter the filename be sure to specify the full path e.g. /home/import/Myfile.csv

The program will run and provide a report. The beginning of the report identifies any discrepancies such as duplicate product codes, missing units of measure etc. and the remainder of the report will show the products that will update correctly. After you exit the report the system will actually create the products and you can view them in CR.62.

Frequently Asked Questions

Question A:

My spreadsheet looks OK, but when I do “Save as” the .csv file is putting data in the wrong place.

Answer A:

Do a search for commas in your spreadsheet. It is not unusual for the Product Description to contain commas and this will cause a problem. Replace the commas with another character or a blank space. Excel may also insert a comma for quantity delimiters (e.g. 1,440 instead of 1440) .The way to correct this is to highlight the column and format the cells as Text.

Question B:

I tried your solution but there is still a problem.

Answer B:

Check your spreadsheet columns to make sure they show all the letters A,B,C etc. If you received this spreadsheet from someone else it is possible that they have hidden some columns so they don’t display (use the command Format / Column / Unhide)

Question C:

When I run CR.70 I get an Error 42.

Answer C:

This is probably due to unexpected data in the wrong column – see answer to a) above.

Question D:

I need to import products with more than one billing profile but the import doesn’t have a place to specify the billing profile.

Answer D:

Separate your data by billing profile and create a separate spreadsheet for each one; e.g., Myfile1 is for billing profile BP1 and Myfile2 for billing profile BP2. Create template products for each billing profile then run CR.70 once for each billing profile, specifying the corresponding file and template product.

Preconditions

The following codes need to be defined in these jobs:

CR.10 [Measuring Units],
CR.32 [Generic Classification Codes] and
CR.05 [Sales Group Codes].

These codes only need to be set up if they will be provided in the file.

If you are importing products for the first time remember that:

• The client must be setup in CR.06 and CR.18
• A default lotset must exist for this client in WP.46 or else the system will use the default lotset for ALL clients and ALL products
• A template product (typical product) must be setup manually in CR.62 (make sure this product is not included in your import list). The import process will use the template product to copy in additional parameters that are not present in the CSV import file.

A utility is required to copy files from your desktop to the server. One of the ways to do this is to use a freeware program such as WinSCP. You can download this application from the following link: http://www.winscp.com or perform an Internet search to find out more information on secure file transfer between a local and a remote computer.

Subsequent Actions

If the default Lot Set is not appropriate for the new products then new Lot Sets made be defined and attached to products using the following jobs WP.43 [Global Lot Components], WP.44 [Lotting Data Sets] and WP.46 [Lotting Assignments].

Program Fields and Functions

To review a text version of the field help, click here. Click thebutton (or press F5) to invoke field help at any time from within the job panel itself.