|
Madics DART & DVerto |
|
Importing data to use with DART
There are a few ways to get
data into DART one is via a CSV file directly placed in the DART import folder
or if your using
Ashell data to DART with a manually CSV Import
Ashell data to DART using DART.SBX and a CSV file
Ashell data to an external database for DART using DVerto
Possibly the simplest way to
import a CSV into DART is manually copying the file in the DART’s CSV
processing folder, but first you need to go into DART Settings and enable the CSV to DB Table Import option.
This will also ask if you wish
to point DART to the MS Access CSV import Database, the CSV folder and database
is normally under Windows XP located at:
C:\Documents and Settings\{username}\My Documents\My DART
Saves\DART Application Data\CSV
Once selected you will
notice a new CSV Import option on the
DART main tool bar menu, Clicking this will cause DART to search for any CSV
files in folder as stated above, If your unsure of the folder destination or
would like to see what files exist just select the Open CSV Folder this also make it easy to copy any CSV files in.
DART requires a .DEF file
with the same file name prefix as the CSV layout structure, you can find more
about this in the section Ashell data to DART using
DART.SBX and a CSV file but for the time being DART is able to create a DEF
file for you as best it can by selecting the Create DEF option.
DART will read the first
line of the CSV file an list all the fields in the CSV file, here you are able
to change the data type and field lengths, Tick the checkbox at the bottom if
the CSV contains a header record, once complete save the DEF file and
exit. You wll now be able to select/tick
the CSV file and import data.
Before you can pass a CSV
file to DART you need to tell DART the CSV fields and field types, this is done
via DEF file. The DEF file is in a INI
file style format as followings:
[COLx]
NAME={Field Name}
TYPE={String,Date,Numeric,Currency}
SIZE={length if string}
So for example if you have a CSV file containing a
Customer Name, Last Order Date and outstanding b
FRED and Co
Limited,071225,500.32
etc..
and the DEF file will be as
follows:
[COL1]
NAME=Customer_Name
TYPE=STRING
SIZE=35
[COL2]
NAME=Last_Order_Date
TYPE=DATE
SIZE=6
[COL3]
NAME=Outstanding_B
TYPE=NUMBER
The DEF file requres the
same file name prefix as the CSV file, so for eaxmple if we have an HISSAL.CSV
the DEF file will be called HISSAL.DEF.
Once we have the data and
field information we use the DART.SBX to view the indormation in DART, the
XCALL format is as follows:
XCALL
DART,{opcode},{rtncde},{Csv File},{method},{value field},{group field},{chart
type},{display type},{transfer files},{chart title}
Opcode 1
– Launch DART and import CSV.
2
– Check if DART is installed on PC
3
– Launch DART as if you clicked on the DART Icon.
Return
Code 0 - Failed
1
– OK
CSV
File The CSV File name
1-Count
Value
Field The value field to show
in the chart – Y Axis. Eg. Invoice Value.
Group
Field The group field in the
chart – X Axis, for example Customer Code.
Chart
Type 0 – Pie
1
– Bar
2
– Line
3
– Area
Display
Type 0 – Launch and show chart,
then exit.
1
– Once users finished with the Chart stay in DART
Transfer
Files 0 – DART will copy/FTP file
to DART folder
1- Your manually place the files
in the correct DART folder.
Chart
Title The Charts Title.
Once the SBX has been called from
Recommend looking at the example in the
DVerto that come with DART is an application that
takes
DVerto uses script and configuration files stored on
the
Two files are required for each data file a .LAY and
a .DEF (The DEF file is different from the .DEF file used for CSV imports)
the .LAY file contains the data files table,
description and file location on the
For eample we have a datafile called INTAKE.DAT
The INTAKE.DEF file will be something like:
[SETTINGS]
TABLENAME=Order_Intake
DESCR=Sales Order Intake Example
MAPFILE=INTAKE.LAY
DATAFILE=/madics/miame/dsk43/280006/intake.dat
RECORDSIZE=256
The following set the records in use count position on the header (first) record the
following is the default and gets the in use count from a
floating point at byte 9, but a STRING type can also be used.
[HEADER]
INUSE
TYPE=FLOAT
INUSE
BYTE POS=9
INUSE
BYTE LENGTH=6
If the AMOS
random data file does not have the “records in use” as a floating point (F,6)
in postion 9 of record 1 then you can also add HEADER RECORD=NONE
The
.LAY file contains the AMOS file structure. You do not need to include all
fields which is useful if you want to exclude sensitive or unimportant fields
(costs or Production Factor for example).
The INTAKE.LAY file will be something like:
1,Order_Number,S,6
FILTER,Order_Number,<>,"]]]]]]" This
filters out records with ‘brackets’ if NO HEADER is used.
7,Customer_Code,S,6
3,Order_Or_Credit,S,1
14,Product_Group,S,15
29,Part_Number,S,15
44,Location,S,2
46,Description,S,40
86,Order_Quantity,F,6
92,Net_Value,F,6,100
98,Delivery_Date,D,6
104,Order_Date,D,6
110,Sales_Rep,S,3
113,Record_Type,B,1
114,Customer_Name,S,30
144,Area,S,3
147,Product_Category,S,2
149,Cost,F,6,COSTDIV
155,Manufacturers_Ref,S,25
194,Madics_User,S,6
Far more information on the LAY file struture and
SQL execution scripts can be found here.
Once the DEF and LAY files have been create you need
to setup and point DVerto to these files, launch DVerto and under the Madics/
You will also need to set the Local Database in
DVerto to a new database in your SQL Server or a MS Access database.
For a more detail example on setting up,
configurating and running DVerto goto the “example setup to the Madics/
Please ask Madics for a small working example of an
INTAKE.DAT, SALMAN.DAT and the LAY & DEF Files, these are normally in the
[280,6] ppn.
Once the data has been extracted from