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.

Back to top


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.

Back to top


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.

Back to top


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.

Back to top


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.

Back to top


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

Back to top


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
"Variable=value"

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=
entries specify which SELLmatix masterfiles will be updated by the imported data. Up to 9 different masterfiles can be updated by a single import, but normally only one masterfile would be updated during an import. If, for example, the imported data contained both Department and Product information, then more than one masterfile may be updated during a single import.

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=
Tells SELLmatix where to start processing the file. Valid entries include

0 meaning the first byte of the data.
1 meaning after X bytes of data.
2 meaning after a certain character appears in the input data. 3 meaning after the first line of data. This is useful if the first line is a header.

StartOffset=
specifies the number of bytes from the beginning of the file to start at, if StartAt=1.

RecType=
tells SELLmatix how to separate one record from another in the import data. Valid entries include:-

0 meaning that the record is a fixed length in bytes.
1 meaning that the records are delimited by a character indicating the start of a record.
2 meaning that records are separated by a carriage return/line feed combination.

FieldType=
This field tells SELLmatix how the fields in a record can be extracted. Valid entries include:- 0 meaning that fields are a fixed number of bytes in length which is specified for each field.
1 meaning that the the fields are delimited (separated) by a particular character.

FieldDelimChar=
This entry specifies the delimiter character used to separate fields within a record. This entry is ignored unless FieldType=1 and can be "Tab" or "Comma". The default is Comma.

NumberOfRules=
Rules are used to determine when a record will be imported into SELLmatix as well as what SELLmatix database will be updated for a particular record. Rules are particularly useful where more than one database is being updated from a single import file. For the data being imported contains both department records and product records, rules are used to tell SELLmatix whether a particular record on import is a department record or a product record.

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=
"N" is a value from 1 to 9 indicating the rule number. The data after the "=" sign contains the rule and the syntax for the rule is:- FIELD,OPERATOR,TYPE,VALUE,ALIAS

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=
contains a list of fields, separated by commas which SELLmatix needs to extract from the input data. These field names are the names of fields in the SELLmatix Databases being imported. Case is significant as are whitespace characters. That is there should be no whitespace characters preceding and following the field names and the spaces within the field names should be exactly as they are within the databases.

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 PRICE
Then 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=
This entry is a flag which indicates that the data in the field is the primary index key. If this value is set to "1" then the master index is updated containing the data in this field as the key. This field should only be set where an index is maintained, and only for the one key field.

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=
This entry can be used in two ways. Where DataType=1 this entry is required and contains the number of bytes from the beginning of the record where the field begins.

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=
This entry can be used in two ways. Where DataType=1 this field is required and contains the number of bytes from the beginning of the record where the field ends. This value must always be greater than the StartOffset= entry for the field.

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=
This entry is optional, and specifies the data to put into the SELLmatix field if the input does not contain a value. ie. a blank delimited field.

ReplaceChar=
This entry is optional, but if present, then a WithChar= entry is required. The ReplaceChar entry contains a single character which, if present in the input data is replaced with the WithChar value. This is useful if say, the input data has a flag such as "X" to indicate "true" or "yes" and this needed to be replaced by a "Y" in SELLmatix.

WithChar=
This entry is optional, but required if there is a ReplaceChar= entry for this data field. This entry should be set to the character that will replace the ReplaceChar= in the particular field when imported into SELLmatix.

CopyData=
This field is required where DataType=4, and contains the string literal that should be copied into the SELLmatix data field for every record.

NoLeadingZeros=
This entry tells SELLmatix to truncate leading zeros from the beginning of the data when copying the field to SELLmatix. This option is particularly useful if the input file has fixed length fields with starting and ending offsets, and the data is left padded with zeros. If such a field happened to be an index key, this would be required.

This flag can be set to true by assigning a value of "1" to this entry. Otherwise this option is turned off.

MathOperator=
This entry is optional and contain "*", "/", "+", or "-". Where this entry is used, the input data will be converted to a double float value and then a mathematical operation will be performed on the data. The operation performed will depend on the MathOperator value, being multiplication, division, addition or subtraction.

MathArgument=
This entry is optional but required if a MathOperator= entry exists for this field. This entry contains the value that will be applied to the contents of the field by the MathOperator. For example, if the data imported contained the text string "68753", the MathOperator was "/" and the MathArgument was "100", the value "687.53" would be stored in the SELLmatix database field.

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.

Back to top