Settings to describe an import file containing product information
These values are used to describe the format of external files for import a list of quote requirements. At the main screen:-
● Select: Parameters - Quote import parameters
The Quote import parameters dialog is displayed.
Select the Quote you wish to import or create a new one.
A parameter dialog is displayed.
- Review or enter new details for the file you wish to import:
Number of header lines - This is used to specify how many lines to skip before the actual data is found in the import file.
Header line for fixed quote information - This is to nominate which line in the import file is used for the fixed header information (e.g. optimising parameters, saw parameters etc...)
Header line for quote header details - This is to nominate which line in the import file is used for the header details shown in the grid.
File format - This is a combo which is used to select either CSV files or XLS based files.
Extension for CSV file - This is used to specify the file extension for the import files. This is not required for XLS based files.
Field separator - This is used to specify what character is used between each field in a file. This is not required for XLS based files.
Excel sheet name - This is used to enter a specific sheet name to use during the import process. If this is left blank then the first sheet in an Excel file will be used, if the Sheet name does not exist in the XLS file then the first sheet will be used.
Path - This is used to specify the location of the import files.
Use the cursor keys or 'Enter' key or mouse to move between parameters. Overwrite the existing values to make a change. The field positions can be specified as either numbers (0 to 999) or in the Excel column style (A,B .. Z, AA, AB etc....)
The parameters are used to describe the layout of the fields in the import file - which field is the product code, which field is the product description etc.
Basic quote information
Item section
The first group of parameters are for item information. An item can be a product, part, fitting, operation or from the phrase table. Not all of them use all the available fields. Enter the position of the item (if desired) to match the position of the data in the import file.
Fields at the quote screen:-
The above data is imported from the following file:-
0,BASE-SINGLE,Single base unit,500,870,600,7,,,
0,BASE-SINK,Sink base unit,1000,870,600,2,,,
0,WALL-DOUBLE,Double wall unit,1000,750,300,5,,,
Using the following parameters
1.Item type ------ 1
2.Code ------ 2
3.Information ------ 3
4.Quantity ------ 7
13.Product Width ------ 4
14.Product Height ------ 5
15.Product Depth ------ 6
Header section
The header line contains all the customer and quote data that are not product, part, fitting, operation or from the phrase table. This is one off data such as an address of contact name. This is the same format as product requirements but more extensive.
As with product requirements, the header line should contain the following information in the fixed order shown below:-
File name
Order description
Optimising parameter list
Saw parameter list
Overs percentage
Following this will be the rest ( if any ) of the header data as specified in import quote parameters. Import parameters specifies the position of the data in the header line.
To import the following quote data
Use the following header line
quote file,sample quote file,DEFAULT,DEFAULT,,CS1001,Kitchens Direct,Ashford Road,Birmingham,B11 2RX
with the following quote import parameters header setup
Customer Code ----- 6
Customer Name ----- 7
Invoice address 1 ----- 8
Invoice address 2 ----- 9
Invoice postcode ----- 10
Delivery address 1 ----- 8
Delivery address 2 ----- 9
Delivery post code ----- 10
Data for variables
More complex products may contain variable data. Variable data is information that changes for each item or customer e.g. the delivery date or type of door handle.
If the external file already contains the answers for this variable data this can be specified in remaining variable lines.
The # symbol is used to indicate the item is in the imported header line and the number indicates the position in the header line.
Import file (complex.txt)
Combining the two sections could produce an import file such as this
quote file,sample quote file,DEFAULT,DEFAULT,,CS1001,Kitchens Direct,Ashford Road,Birmingham,B11 2RX
0,BASE-SINGLE,Single base unit,500,870,600,7,,,
0,BASE-SINK,Sink base unit,1000,870,600,2,,,
0,WALL-DOUBLE,Double wall unit,1000,750,300,5,,,
For the import to be correct the relevant products and variables must already be set up in the product library and variables table.
Import from Excel files
Set the extension for CSV file to XLS / XLSX from the drop down list.
If there are two files with the same name (e.g. IMPORT.XLS and IMPORT.XLSX) the XLSX file is imported.
Field separator - enter an ASCII value for character defining each field e.g. '44' = comma
Special fields
The following require a number to identify the type of data to be used
Status
Not set, blank or 0 = blank
1 = Estimated
2 = Order placed
3 = Order dispatched
4 = Invoice sent
5 = Prices not valid
Item type
0 = Product
1 = Part
2 = Free form/phrases
3 = Fitting
4 = Operation
Do not print
0 = Print
1 = Do not print
Line Properties
0 = Normal
1 = Detail
2 = Subtotal
Other options
Print - print list of parameters. Program prompts with the standard Windows print dialog so that print settings can be checked before printing begins.
Notes
It's possible to import just the header data or just the product data as desired
Importing a product requires an existing product to be available from the product library. Import will match up the existing product.
It's recommended to specify an item type for each type of data. Without an item type import will not know which type of item to import.
