Overview

SELLmatix allows you to export data from the SELLmatix databases so that the data can be used by a range of other applications. Depending on how your business operates, it may be necessary to do this on a regular basis, or it may be necessary to export the data in a range of different formats, or it may be that you never need to export your SELLmatix data at all. SELLmatix allows you to set up templates for a range of different types of data export which can be used again and again, so that once you have determined the data to be exported and the format in which it will be exported, you can repeat the export operation simply by selecting the type of export you wish to perform. Selecting Output Formats 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. Back to top

Changing the Selection Options

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. The name of this configuration file is "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... ExportTranTypes=QuickBooks (*.IIF),*.iif,MYOB (*.txt),*.txt ExportTranFormats=QBTRAN.EXP,MYOBTRAN.EXP These are lines of text, with fields separated by commas. The first line, "ExportTranTypes=" 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 (*.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, "ExportTranFormats=" 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

Transaction Template Layout

SELLmatix stores transaction data in a comma separated variable file called tran.csv. This is a Unicode text file which resides in the same directory as your SELLmatix data files. This file is extremely verbose, and is designed to contain all the information which any accounting system could possibly want, in a manner which makes it reasonably easy to extract for most accounting systems. The way that different accounting systems read and store data varies widely and often defies logic. Information which is critical to some accounting systems is completely ignored by others, or at least derived in a different manner. The layout of the tran.csv file is not designed to represent the data at the pos in the most logical manner, but rather in a manner that allows the data required by other software to be extracted with the least pain. In almost every instance, when translating data in the tran.csv file to a format used by another application, large parts of the data available will be ignored. This is by design, because SELLmatix needs to be able to work with a range of other software, not just what you are using. SELLmatix allows the raw data in the tran.csv file to be translated into the format used by other packages. Once a translation template has been created and validated, the template can be used to quickly and easily translate the SELLmatix transactions into the format required so that the transaction can be imported into other packages such as QuickBooks, MYOB or almost anything else. Custom software applications will probably want to manipulate the tran.csv file directly, and can even interrogate and control SELLmatix directly through TCP/IP. The transaction export template works like an .ini file, and contains four sections. You can add comment lines to the transaction template by placing a hash character "#" as the first character on the line. Lines in the template which begin with a # are ignored when processing the template. The transaction export template is a Unicode text file, and must be saved in Unicode format, even if you are using the template to export ANSI data. Back to top

Export Template [Export] Section

[Export] Section The [Export] Section of the transaction export template contains general information about the export to be performed. Valid entries include:- Charset= Valid entries for this entry are "Unicode" or "ANSI". The default is ANSI. DateFormat= valid entries are "DD/MM/YY" or "MM/DD/YY" or "DD/MM/YYYY" etc. ProgressTitle= Contains the title of the dialog showing the progress of the Export. Back to top

Export Template [Header] Section

[Header] Section Lines in the [Header] Section are processed before any records in the raw transaction data. Up to 10 lines can be interpreted in the header section to produce output in the output file. These lines are named:- Header Line= Header Line1= Header Line2= ... Header Line9= The header section is normally used to output column headings in the exported data. Non printable characters can be included in the output by representing them as a decimal escape sequence consisting of a backslash followed by the decimal character code. For example an upper case "A" character could be output by the sequence \065 or a TAB character could be output using \009. Back to top

Export Template [Detail] Section

[Detail] Section Entries in the detail section are processed for each record in the raw transaction file. Entries in the detail section are made up of a keyname=data. The keyname to the left of the equals sign is used to determine if the entry should be processed. The data to the right of the equals sign is used to format the data if this line is processed. The first field of the raw transaction data is a record type field. The keyname in the export template is compared to the record type field being processed. If the keyname and the record type field match, the data to the right of the equals sign is interpreted and output. Otherwise the entry in the detail section is ignored. For example, Sale Headers in the raw transaction data contain " HH" in the record type field. If you wished to process Sale Header Records in the raw transaction data, you would add an entry to the [Detail] Section of the template which began with HH= If you wished to process more than one line of information from a single transaction header record, you could have multiple entries in the translation table as follows:- HH= HH1= HH2= HH3= ... HH9= Another example is for product sale records in the raw transaction data. Product Sale records have multiple sub-type depending on whether the item is priced by the unit " PU", weighed by an electronic scale "PW", or whether the price is encode in the barcode label for the item " PB". In this case you could process these types of product sales in a different manner for each by adding the entries... PU= PW= PB= or else, if you wished to process these all the same way, regardless of the subtype, you could do this with a single entry as follows:- P?= In this case, all "P" records would be processed the same way, regardless of subtype. One limitation in using the "?" wildcard, is that you cannot have multiple lines containing wildcards, so entries such as " S?3"," S?4", " S?9" are not valid. Records in the raw input file where the Record Type Field has no corresponding keyname to the left of the equals sign are ignored and do not appear in the output file generated. Interpreted Data to the right of the Equals Sign When a record in the raw transaction file has a matching key in the template, the data to the right of the equals sign in the template is used to interpret and format the raw data and write the formatted data to the output. Non Printable Control Characters Non printable characters can be included in the output by representing them as a decimal escape sequence consisting of a backslash followed by the decimal character code. For example an upper case "A" character could be output by the sequence \065 or a TAB character could be output using \009. These control characters are normally used to separate fields in the output if producing a comma separated or TAB delimited file. Data Fields Data Fields in the raw data can be output by including the field number in % characters. For example, to output what is contained in field number 37 of the raw transaction file, you would put %37% in the template. If you need to output an actual % character from the template this would be represented by including a %% in the template. Output Functions Output functions are used to manipulate data in one of the data fields. Output functions appear between a beginning "|" character and an ending "|" character. If you need to output a literal "|" character from the template, then this can be done by using an "||" sequence in the template. Case is significant in function names. DATEFORMAT Function The DATEFORMAT function is requires a Universal Date Format field as a parameter. This is normally field %9% in the Raw Transaction Data. The DATEFORMAT function formats a Universal date in the manner of the DateFormat entry in the [Export] Section of the template. An example of the DATEFORMAT function would be "|DATEFORMAT(%9%)|". SIGNREV Function Some accounting packages use negative numbers to differentiate between debits and credit, others do not. In certain cases it is necessary to reverse the sign of a number in the SELLmatix raw data in order to post correctly in an accounting package. Placing "|SIGNREV(%40%)|" in a template would reverse the sign of the number in Field 40 of the data file. MATHOP Function The MATHOP Function performs a mathematical operation on the data in one of the fields in the raw data file and formats the result. An example of the MATHOP Function would be:- |MATHOP(%39%*100"%%.0f")| In this example, the value in field %39% of the raw data file would be multiplied by 100 and formatted. The format string can be omitted, but if used, can be any valid format string used with the "C" Runtime Library printf() function, except that a double percent (%%) sign must be used. If you don't already know what this is, then just omit this parameter. Back to top

Export Template [Footer] Section

[Footer] Section Lines in the [Footer] Section are processed after any records in the raw transaction data. Up to 10 lines can be interpreted in the header section to produce output in the output file. These lines are named:- Footer Line= Footer Line1= Footer Line2= ... Footer Line9= The [Footer] section is normally not used. Non printable characters can be included in the output by representing them as a decimal escape sequence consisting of a backslash followed by the decimal character code. For example an upper case "A" character could be output by the sequence \065 or a TAB character could be output using \009. Back to top