|
Creating a Spreadsheet to Import Into EZ-Catalog
|
|
|
Introduction and assumptions
|
|
|
For those with a little more computer experience, a database program can also be used to create a file to import. As your catalog gets more complicated, a database program may provide greater flexibility. This will become even more true as EZ-Catalog adds more features. However, this article will only cover spreadsheets.
|
|
|
A spreadsheet program can be used very effectively to create a file that can be imported into EZ-Catalog. Excel, Lotus, and Quattro Pro are spreadsheet programs that can be used. For most of the work, only rudimentary spreadsheet skills are required. We assume that you know of how to enter data into the rows and columns of the spreadsheet. Only the exporting of the file from the spreadsheet is a little tricky but once mastered is easy to do.
|
|
|
Setting up your spreadsheet rows and columns
|
|
|
The first row of your spreadsheet should contain names of the data fields such as name and stock number. The link below will take you to a page of all possible data fields used in EZ-Catalog. You only need to use the data fields for which you have data. So, if you do not have data for particular data field, do not create a column for that information. For your convenience, you can also create a column of your own data that will not be imported. (For example, a column defining the sort order for the product.)
|
|
|
|
|
In the remaining rows of the spread sheet you should add a row for each product in your catalog. When you are done you should have one more row than you have products, the first row being column titles. For your convenience, we have created a simple spreadsheet file that has the basic columns defined and some sample data so that you can see what it should look like. This spreadsheet may also serve as a beginning for your own spreadsheet.
|
|
|
|
|
If you have product options and values
|
|
|
If you are using product options and values, you will need to add additional rows in your spreadsheet. You will need a row for each product option and product option value. In other wards, if the product had four sizes and six colors for each size, then you will need to create 10 rows for this product. There are several acceptable formats described below for associated rows.
|
|
|
Duplicate all product data. You could duplicate the entire row of data for the product for each option and value. Then the data in the columns for the options would vary with each option and value combination. The import program will read the data from the first row for a product and ignore it for all others. The advantage of this format is that the import file could be in any order you wish.
|
|
|
Indented product data, standard. You could put the complete information about a product in the first row in the series and leave out the duplicate data in the rest of the rows associated with the product. For example, you could define the title in only the first row of the series and leave it blank for the rows defining the associated options and values. Regardless, you should have the product number in each row. In this style you would duplicate the option title in each associated row with each value. As long as you have the product number and the option title in each row, the import program will always combine all product options and values together regardless of the order the rows are imported. This format is the recommended system because it is easy to read yet, each row is fully qualified.
|
|
|
Indented product data, minimal data. This format is similar to the one above, except, you do not duplicate any information, not even the product number or option titles. This format is a very clean looking format but the order of the rows is critical. In this case, since the product number is not duplicated in each row, the importing program will assume that the row without a product number is associated with the row above it. The same with the option values. Since you might get unexpected results from this format, we do not recommend it.
|
|
|
We have created a sample spreadsheet that has product options and values defined. It also has some sample data. You can use the spreadsheet to understand how the file could be defined and as a template to start you own file.
|
|
|
|
|
|
|
|
|
Using commas in product titles and descriptions will work fine within your spreadsheet. However, since each of these punctuation marks have double meaning when it has been exported, you must avoid using them within your spreadsheet. If not, your data may not import as you think it should. However, there are coded characters you can substitute for these characters that can be manipulated to give you the desired results.
|
|
|
Every where you would use a comma in your product titles and description, substitute another set of characters. The characters you choose must be unique and not have any other meaning in your data. In our instructions, we assume that you have used @@ for each comma. The following sentence is an example. Look@@ look@@ see Jill run. This may look and sound strange. However, in a later step you will understand better what we are doing so that when the data actually gets to your online catalog, it will look normal.
|
|
|
Saving your data and spreadsheet
|
|
|
As you are building your spreadsheet it is a good idea to save a copy to disk regularly. We suggest that you save the spreadsheet in the native format for the program you are using. If you choose to maintain your catalog with as a spreadsheet, uploading to EZ-NetTools as change are made, then we strongly suggest that you make a hard copy of the file on a floppy disk or CD.
|
|
|
Moving your data from the spreadsheet to EZ-NetTools
|
|
|
After you have your catalog completely defined in your spreadsheet, you can now transfer than Getting you data from the spreadsheet to EZ-NetTools is a three step process. First, you export the file from your spread sheet program. Second, you will modify all the delimiting codes in the file in a format the importing program is expecting. Third, you import the file into EZ-Catalog.
|
|
|
Exporting your data from the spreadsheet program. Each spreadsheet program has a slightly different export process. The export process will go something like this. Under the file menu, you select the “Save as” option. You will specify the file name and the file type. Look for file types like, ASCII, CSV(Comma delimited), Text (but not tab delimited), or unicode text. These file type are similar. The result of this step is a file that can usually be read by almost any other program.
|
|
|
Viewing and manipulating text file. Open the file created above with a very simple text program such as Notepad. If you have a very large catalog you may have to use Wordpad. If you do, you need to be very careful to save the file as a simple text file. Many of these text editing programs add special characters to the file if you are not very careful. (For that reason MS Word may be too sophisticated to do this step. It adds fancy formatting codes to the file.) Do the following steps.
|
|
|
First, go to the last line of the file. Keep trying to push the cursor to the last character. When the cursor is at the last character in the file, it should be pointing to the last character on the last line. If the cursor is on a blank line, then backspace until the cursor is immediate after but on the same line as the last line. To repeat, if you have a blank line at the end of your file, you must remove it. Excel tends to do this.
|
|
|
Next, we need to change all the commas that delimit fields to be &&. (You do not need to do this if you have not used any commas in your data fields. However, it is a safer bet that there are commas in your data.) You change all the commas by using the search and replace feature of note pad.
|
|
|
Next, now that all the commas that delimited data fields have been changed, we can change the coded commas in the text to real commas. Again use the search and replace feature to substitute the @@ for a comma. Using the example we talked about above, the phase “Look@@ Look@@ See Jill run” will now become “Look, Look, See Jill run.”
|
|
|
Finally, save the file. You may want use a new file name so that you can have your datafile at each stage of the process. You now have a file that you can import into the EZ-Catalog.
|
|
|
|
By Jim Burres - Technical Support Representative - Nov 2003
|
|
