Connect by Dynamic File Format
You can use the WinServ Data Import and Export Tool to produce data files with WinServ records or to import files from other systems into WinServ. The tool can also be scheduled using the Windows Task Scheduler, for continous data integration.
Supported File Types
- CSV / Text
- XML
- JSON
- Excel Spreadsheets
How to import
Here we are going to explain a simple way to import data into WinServ.
Example below of a text file with tab separated fields.
Product Number Product Description Price 123 Gear 1.23 666 Bottle 6.30 789 Key 1.50
The text file below contains WinServ headers (table.variable) which will make it easier to import. (This can by done in any view in WinServ and by right-click in a field and select Copy Field name and inserted at first line as header)
ARTIKLAR.ARTNR ARTIKLAR.BEN ARTIKLAR.UTPRIS 123 Gear 1.23 666 Bottle 6.30 789 Key 1.50
- Start the DataImportExport.exe
- You will then see the start screen.
- Select File and Select database
- Browse to the correct database and the datafolder, you want to import to.
- When the database is activated you will see the complete search path in the top.
- Select File
- Select New file import
- Now the first import screen will show up, step 1/4.
- Select Source file by browse to the file you want to import.
- Click Open to open the file. Field turns into green if the seachpath is ok.
- Now check if you need to change other setting like Date format, Date separator or Decimal separator.
- Select Preview, step 2/4, to verify that the file has been parsed correctly
- Select Mappings, step 3/4. Note, if you do not have WinServ file headers it is now time to pick fields from WinServ in appropriate view, in this case product view, by right-click in the product number field and select Copy Field name and insert them into Target column. Repeat this procedure for all fields. You can only pick fields from table at the time for each import.
- Select Import, step 4/4.
- Click Import button to begin the import
- You will get meassage when import is ready.
How to export
In this example we will export product data to a textfile and Excel.
- Start the DataImportExport.exe
- You will then see the startscreen.
- Select File and Select database
- Browse to the database and the datafolder, you want to export from.
- When the database is activated you will see the complete search path in the top.
- Select File
- Select New file export
- Now the first export screen will show up, step 1/3.
- Select Destination file by browse to the file you want to export. In this case a text file.
- Select File type. In this case a text file. You can also select Excel, if you have Excel installed.
- Select also other settings like Date format, Date separator or Decimal separator.
- Select Mappings, step 2/3.
- There are two ways of mapping data fields to the columns: In this exampla we choose 3 fields: product number, product description and sales price. A. By Click Add field mapping for each row and insert WinServ tablename in Alias and File field columns. B. By Click Load table structure which gives you the possiblity export whole table.
- Select the Export button, step 3/3.
- Enter the table name in this case ARTIKLAR (Eng. Products), it will become green if you type correct name of the table. The table name is Word on the left side of the comma. 15) Start the export by click on Start export.
Start Parameters
The database import and export tool can be started automaticlly by adding switches after Calling DataImportExport.exe.
Parameter | Purpose |
---|---|
/autorun |
Will start the application without the GUI. |
/datapath:<folder> |
The database to be used. |
/regex:<regex> |
If a folder is passed as target then you must provide a regex for the file selection. |
/keepfile |
Do not delete the file after a successful import is made. |
/backupfolder:<folder> |
A folder that imported files can be backed up to. If /keepfile is used, the file file be ''copied'' to the backup folder, otherwise it will be ''moved'' to the backup folder. |
/backupmode:<mode> |
Use /backupmode:1 when you want the backup routine to set a timestamp on the filename in the backup folder. This mode also creates an own sub folder for each database and wsx file. This mode is deafult. Use /backupmode:2 when you want to backup the filename without changing the filename to the backup folder. This mode will replace any files in the backup folder with the same name. |
/logtotextfile |
Log records will be appended to {dbpath}\data_import_export\yyyy-MM-dd_log.txt instead of the standard logging to LARGELOG |
<file>.wsx |
The .wsx-file to be used. |
/target:<path|file> |
A target file or target folder. Can be a filename for an export or a filename/folder for an import. |
Example of how to start with parameters
DataImportExport.exe /autorun "C:\Data\data_export.wsx" "/datapath:C:\Data" "/target:C:\Data\data_import_export\fil.txt" /keepfile "/backupfolder:C:\Backup" /backupmode:1
Update mode
Update mode is set in the import tab and is saved in the wsx file. It can not be set as a start parameter.
Update mode | Description |
---|---|
Overwrite | Create new record or overwrite the existing record in the database. |
Skip if found | Only create new records, never overwrite any existing records in the database. If an existing record is found it is skipped an the import continues on the next record. |
Overwrite - don't import blank values | Similar to overwrite, but the fields of existing records in the database never gets blanked out if the source file has blank columns. This mode has nothing to do with not importing blank records, for example a blank customer id (use a condition if you don't want to import blank records). |
Overwrite - non unique index | This update mode is intended for creating new and updating existing records when the source file does not contain the fields of any unique index in the table. Each row in the source file will either create exactly one record or update exactly one or zero records. In other words, nothing will happen if more than one record are matching the non unique index. |
Functions available when exporting and importing
These functions may be entered in the Value/Expression column in the mapping grid.
Example | Value / Expression | Description of the example | Definition |
---|---|---|---|
1 | "P" + Sequence("SYSFIL.KUNDNR") | Insert the character P then append with a generated customer id. | Sequence(file field) |
2 | Left(this, 2) + "AB" | Insert the 2 leftmost characters of the value from the import file and append AB. | this |
3 | IF(this="","2099-12-31",this) | Insert 2099-12-31 if the column data from the text file is empty, otherwise insert the column data from the text file. | IF(bool expression, true expression, false expression) |
4 | FromLeft("KUNDER.NAMN", 10) | Insert the 10 leftmost characters of the current KUNDER.NAMN buffer value. | FromLeft(value, length) |
5 | FromRight("KUNDER.NAMN", 10) | Insert the 10 rightmost characters of the current KUNDER.NAMN buffer value. | FromRight(value, length) |
6 | Counter() | Row number in file. For example: 1, 2, 3... | Counter() |
7 | Counter()+999 | Row number in file plus 999. For example: 1000, 1001, 1002... | Counter()+value |
8 | CurrentDate() | Inserts the current date. Date formatting settings from the Settings page will be used. | CurrentDate() |
9 | Lookup("KUNDER.COUNTRYCODE", "LAND.KOD", "LAND.NAMN") | Will use the current value in KUNDER.COUNTRYCODE to make the match. | Lookup(value, matching file field, return file field) |
10 | Lookup(this,"MSTATUS.NR", "MSTATUS.NAMN") | Will use the current column value to make the match. Import only. |
Error Messages
- SQL_UPDATE cannot update primary key
- Make sure you are using capital letters in the import file for table and column names, KUNDER.KUNDNR
Filter import/export
When you are at the export-stage, in the field "Constrain statement" you can fill in condition for the export (or import).
Exemple:
Filter Machines/objects with status 0 - Active (MASKINER.SERV_CACHE
) and the customer not is blocked (KUNDER.SPARR
).
Table: MASKINER
Constraint statement: ((MASKINER.SERV_CACHE=0) and (KUNDER.SPARR=0))