DATA IMPORT |
---|
Links on this Page Overview Types of Data to Import Import One Time or Regularly Using Standard Import Templates |
Overview |
SELLmatix allows you to import the data which is needed to process sales from a range of sources easily and quickly.
The problem with importing data is that there is an almost infinite number of ways in which your data may be stored
before you import it to SELLmatix, and so the number of ways in which data can be imported is almost endless. While SELLmatix will allow you to add products "on the fly" while you are processing sales, if you have your product, customer and department data already stored in another format, then you will save a great deal of time if you are able to import this rather than re-key the data manually. In other cases, you may be able to obtain data files from wholesalers or other stores which contain all or a significant part of the information you need. This is particularly useful if you that data contains barcode numbers and descriptions. On the other hand, if you don't have a product master file and can't get one, then we strongly recommend that you enter your product file using SELLmatix by selling items "on the fly", instead of keying products manually. There are 2 reasons for this. Firstly you do not need to key the barcode numbers, you simply scan the item and then enter the other details. Manually keying barcode numbers is error prone and tedious. Secondly, when you add items in SELLmatix "on the fly", you get an immediate benefit, because you can then sell that item straight away, and this saves time. Adding products "on the fly" while processing department sales in SELLmatix is allows you to sell goods the same way as with a cash register, and the masterfiles can be built up quite quickly. |
Types of Data to Import |
There are 3 types of data you may wish to import. These are your products, your customers and your departments. If you do not run charge accounts for your customers, then there is no need to import customer data. If you do not already have an accounting system that uses departments, then it will probably be simpler to just key your departments in SELLmatix manually. If at all possible, however, you should find some way to import your product masterfile as this can make a significant difference. |
Import One time or Regularly |
In some cases, you will want to import your data into SELLmatix one time only, and use SELLmatix as the central controlling point for your data. In other cases you may
wish to have another software package as the main controlling point, and simply supply SELLmatix with the data it needs to process sales. In that case, you will make changes to
your main data files in the other software package, and will need to import that data into SELLmatix on a regular basis, after changes have been made. SELLmatix allows you to create templates which tell it how to import data from almost any type of file. Creating these templates takes some work, but once this is done, the importing becomes very easy. If you are going to import data to SELLmatix on a regular basis, then it is worth creating a template which you can use every time. If you are only going to import the data once, however, then it is not worth the effort of creating a template. Some templates already exist and are installed with SELLmatix. There are already templates for importing data from QuickBooks, MYOB and generic Spreadsheets. Bear in mind however that there are different versions of QuickBooks and MYOB in different countries, and slight variations to the templates are required. Some sites do a once off import of products, and then maintain these in SELLmatix, but keep their customer information in another package such as MYOB or Quickbooks. SELLmatix does not process debtor accounts or send statements, but can supply the transactions to other accounting systems so that sales appear on the monthly statement in another accounting package. MYOB and QuickBooks do not handle large product files particularly well. For example, a product file with 20,000 is virtually impossible in MYOB. SELLmatix, on the other had has no trouble at all with 20,000 products, and has run in some sites with over 130,000 products without problem. It is simply a matter of determining the best structure in your case. |
Using Standard Import Templates |
When exporting data from SELLmatix, the Save as dialog appears allowing you to select the filename and other options about the data
to be saved. At the bottom of this Dialog, you have the option to Save File as Type, and a drop down list allows you to select the
type of export to perform.
Select the type of file you wish to import and then the file which you want to import. |
One Time Imports |
Importing data is a trial and error process. Imported data will not work correctly the first time, and when
this happens you will end up with information that should appear in one field appearing somewhere else,
not at all, or in the wrong format. When this happens, you will have no option put to delete the data files and start again, because short of manually correcting the errors, there is nothing else you can do. Before importing your data, make a backup copy of you data directory, end when an error occurs, copy the original data back and start again. While this is not a difficult process, it will usually take 5 or 6 attempts before the import appears exactly the way that you want. If you have data files which are in the same format as used in one of the standard templates, try importing using that template first. Bear in mind that there are so many different versions of file layouts, that chances of the standard template working first time on your data are considerably less than 100%. If a standard template is very close to importing the way that you want, you may wish to modify that template using the information below on custom templates. But if you are only going to do this once, then it may not be worth the effort. |
Adding the File Type to the Open Dialog |
When you select one of these file type, you are really telling SELLmatix which export template should be used to export the data.
The way that SELLmatix knows which export template to use is from the configuration file. "SELLmatix Control.cfg".
This file is stored with your other SELLmatix Data files. You can create new export templates, and change the options that appear in the "Save as Type" list when you go to export data from SELLmatix, by changing entries in the configuration file. You can edit the configuration file using a text editor such as Notepad, but make sure to save your changes in Unicode format. In the [System] section of the configuration file, there are 2 lines for each type of export. These are something like...
ExportPLUTypes=QuickBooks Products (*.IIF),*.iif,MYOB Products (*.TXT),*.txt ExportPLUFormats=QBPROD.EXP,MYOBPROD.EXPThese are lines of text, with fields separated by commas. The first line, "ExportPLUTypes=" entry contains pairs of entries where the first entry in the pair contains the text that appears in the drop down list of "Save as type". The second entry in each "pair" of entries on the first line, contains the wildcard pattern to use to determine which files appear in the list of files above, when that entry pair is selected. For example, if you selected "MYOB Products (*.TXT)" from the dropdown list, the wildcard "*.TXT" would be used so that any file name with an extension.".TXT" would appear in the list of files. The second line, "ExportPLUFormats=" entry contains a list of export templates which SELLmatix will use when exporting. You can change the order in which options appear in this list, add new export templates or remove templates that you will not need. |
Import Template Layout |
Template files are used to tell SELLmatix how to translate raw data in another format into the data format used internally by SELLmatix. Once a template has been created,
it can be used to automatically process input.
Template files are Unicode text files which can be edited with a text editor such as Notepad. These files are similar in
design to .ini files in that there are sections which are enclosed in square brackets, and then a number of statements of the form The variables are key words which SELLmatix looks for when preparing output, and the text after the "=" is interpreted to produce the output. No space can occur between the variable name and the equals sign. Spaces appearing after the equals sign are output as string literals in the output. These export templates must be saved in Unicode format, even if only ANSI output is being produced. [Import] Section
The [Import] Section contains general information about data being imported which SELLmatix needs to know so that it can process this raw data. The format of the
data being imported can very very widely, and [Import] Section gives SELLmatix broad basic information about the raw data.
FileX= SELLmatix masterfiles which can import data are as follows:-
invm.mas inventory master file dept.mas department master file cust.mas customer master file accm.mas accumulator master file clerk.mas clerk master file disc.mas deal discount master file inev.mas input event master file paym.mas finalisation (payment method) master file pos.mas pos master file (control only) tax.mas tax rate master file totals.mas totals master file (do not update this file through import)In addition, SELLmatix maintains indexes on a number of these files. these are:- cust.ind customer masterfile index dept.ind department masterfile index disc.ind discount rate masterfile index invm.ind inventory masterfile index totals.ind totals masterfile index.When a masterfile which is indexed is imported, the index should be updated at the same time. The FileX= setting has the following arguments:- ALIAS,DATABASE,INDEX,REPLACE FLAG,USER PROMPT The ALIAS is name used to refer to this file later in the import script. This makes most sense when importing data to more than one file in referring to a field in a particular file. It is possible that more than one file could use the same field name, and the alias is to indicate which file the filed name refers to. Use of an ALIAS however, is required, even where only one masterfile is being updated. DATABASE is the name of the database being updated. This should only contain the filename. The path to the database is the path used for data files in Creating/Selecting Companies. INDEX is the name of the index being updated. This should only contain the filename. The path to the database is the path used for data files in Creating/Selecting Companies. REPLACE FLAG should contain 'Y' if the template replaces existing records, 'N' if the database should update existing records, and 'A' if SELLmatix should Ask the user if the wish to have existing records deleted before the import. USER PROMPT is used if asking about deleting existing records. Title= is the title displayed in the import Dialog while the import is in progress.
StartAt=
0 meaning the first byte of the data.
StartOffset=
RecType=
0 meaning that the record is a fixed length in bytes.
FieldType=
FieldDelimChar=
NumberOfRules= This entry in the [Import] section of the template tells SELLmatix how many rules to check when performing an import. Up to 9 rules can apply to a single import. In cases where only one type of record is being imported and all records should be imported you would specify that only 1 rule was to be evaluated, and that rule would be set to always evaluate to True.
RuleN= FIELD is a field name as defined later in the import template. The field can be any part of the import data, and does not need to be part of the data imported. OPERATOR is used to compare the data in the field to a value. The operator can be < to indicate that the field data is less than, > to indicate that the field should be greater than, or = to indicate that the field data is equal to the value specified, or "!" to indicate that the field data is not equal to the value specified. TYPEIndicates the type of comparison to make. If this set to "c" then the evaluation is a character evaluation as in a string compare. If this is set to "n" then the comparison type is treated as numeric and the values are converted to double floats before making the comparison. If the value is "t" then the rule is treated as being always true, and no evaluation is performed. VALUE is the value to which the contents of the FIELD is compared. ALIAS is the alias name (specified earlier) of the file to be updated if the result of the evaluation is true. If the result of the evaluation is false no update is performed based on this rule, however another update may occur if another rule evaluates as true.
FieldNames= For each field listed in the FieldNames= entry, a subsequent section enclosed in square brackets which contains information about the field data being imported. For example, if the following entry appeared for the Fieldnames= entry,
FieldNames=BARCODE,DESC,SELL PRICEThen subsequent sections in the file would need to appear containing information about how these fields obtained their data.
[BARCODE] FileAlias=PROD ... [DESC] FileAlias=PROD ... [SELL PRICE] FileAlias=PROD ...Back to top |
Input Field Specification |
For each field to be imported into SELLmatix, a section in the input template is required which contains information about
how the data is to be imported. If no field section exists for a field which does exist in the database, then the data in the
field will be blank for new records, and unchanged for updated records. There are a number of entries which can be made in the template, for each field. Some of these are required and others are optional. FileAlias= (Required) This entry must contain the alias name of the file being updated which is specified in the FileX= entry of the [Import] Section. DataType= (Required) This entry must be set to "1" if the input data is a fixed length identified by a beginning and ending offset in bytes from the beginning of each record. This entry must be set to "2" if the input data is the record number of the input field. This option is not frequently used. This entry must be set to "3" if the input data is field delimited by "Tab" or "Comma" in the input record. In this case the FieldType=1 would also be required. This entry must be set to "4" if the data in the input field is a constant specified in the input template, which is always the same in each record.
IndexKey= FieldNumber= This entry applies where the DataType entry contains the vale "3", and is ignored in all other cases. The first field in an input record is FieldNumber=0, and the fieldnumber increases by one for each delimiter character encountered within the input record.
StartOffset= Where DataType=3, this entry is optional and specifies as starting offset within the delimited FieldNumber where the data starts to be copied into the SELLmatix data field.
EndOffset= Where DataType=3, this entry is optional and specifies the ending offset within the delimited FieldNumber where the data stops being copied into the SELLmatix data field.
DefaultData=
ReplaceChar=
WithChar=
CopyData=
NoLeadingZeros= This flag can be set to true by assigning a value of "1" to this entry. Otherwise this option is turned off.
MathOperator=
MathArgument= Please note that in the event where the MathArgument contained an invalid value, such as zero when the MathOperator was division, an exception would not occur, but the result stored would be the source data without the mathematical operation. |