Transferring data between different software packages is a fundamental requirement, but unfortunately it is one that is overlooked by a great many software houses. For example see our review of Minerva's Ancestry in RISC User Volume 2 Issue 8 where a special routine had to be written to import data. In an ideal world all packages would provide routines that allow data import and data export in commonly used formats.
Comma Separated Value (CSV) format is probably the most common format for data transfer. In CSV format each piece of data is separated from the next by a comma.
As an example let us examine the transfer of data from BEEBUG's own Masterfile package to Logistix. Masterfile has no CSV export facility, whereas Logistix has a CSV import facility.
Logistix is one of the best integrated business software packages available for the Archimedes. It combines the elements of a spreadsheet, timesheet, database and graphics package. Its weakness, however, for Archimedes owners having upgraded from an older Acorn machine, is that their database information from BBC programs cannot be used directly in it. This is a great pity because Logistix is designed to manipulate database information in many varied ways. The program presented here takes files from the BEEBUG's Masterfile database and converts them to CSV format for loading into Logistix. They can be kept in the Logistix database permanently or, if preferred, transported from Masterfile simply to be manipulated in the spreadsheet, graphics and time sheet areas of Logistix.
Masterfile's data format is described in its manual, and can be found dynamically from the "Descriptor" file. In the program, the procedure PROCgetdescriptor reads the field and record characteristics. Matters are kept simple, as all Masterfile data is held as strings. You should be aware that Masterfile fields can contain commas and quotes within them, and these must be removed (or in this case converted to a '\') before conversion to CSV format.
The program is relatively short and is very simple to use. Type it in, save it to disc and then run it in the normal way. The screen will clear and you will be prompted for the Masterfile data path. Simply press Return to accept the displayed default, otherwise enter the required pathname. After you have entered this information, you will be prompted to insert the Masterfile disc, and then conversion will commence.
The transferred indicator on the screen shows the record number being transferred. When this is complete, the new file will then be copied into the Logistix directory of your Logistix disc, and a message appears, instructing you to boot up the Logistix disc and type the commands to load a new file. These commands are as follows:
/L | - | to select load |
C | - | to load a CSV format file |
<name> | - | the name of the database file |
R | - | load the file into rows |
The file will then load and the database will be displayed. When saving the database, the usual 'Logistix' format option can be used.
Logistix has an option to load CSV format files. The program given here makes the Comma, Carriage Return, and Line Feed insertions automatically. Because commas are used to separate fields, and quotes within strings would upset the format, any existing commas and quotes in the data are automatically changed to back-slash characters so that they do not upset the converted database format. If your requirement is for a different character, then alter the character to replace the comma and quote by amending the back-slash in line 680.
Hopefully, you will find that the techniques used in the program can be extended to allow CSV files to be created from packages that do not have this facility.
10 REM >MFtoLGX
20 REM Program Masterfile-Logistix
30 REM Version A 1.04
40 REM Author Richard Kirby
50 REM RISC User September 1989
60 REM Program Subject to Copyright
70 :
80 MODE 12
90 ON ERROR GOTO 960
100 DIM fname$(18)
110 PRINT"MASTERFILE TO LOGISTIX CONVERSION"'STRING$(33,"=")
120 VDU28,0,31,79,3
130 REPEAT:name$=FNresponse("Name of file",""):UNTIL name$>""
140 path$=FNresponse("Masterfile path name",":0.$.MFdata")
150 Ldrive$=FNresponse("Data drive for
Logistix CSV data","0")
160 CLS:OFF:PRINT"Press space when Masterfile disc ready"
170 REPEAT UNTIL GET=32:CLS
180 :
190 PROCgetdescriptor
200 mf=OPENUP(path$+".Masters."+name$)
210 lgx=OPENOUT(":"+Ldrive$+"."+"$.logistix."+name$)
220 PROCwriteheadings
230 :
240 FOR rec=1 TO lastrec%
250 PTR#mf=rec*reclen%+1
260 PRINTTAB(0,0);"Transferred: ";INT((rec/lastrec%)*100);"%"
270 FOR f=1 TO fields%
280 INPUT#mf,field$
290 PROCout(FNstrip(field$),f=fields%)
300 NEXT f
310 PROCcr
320 NEXT rec
330 CLOSE#0
340 ON
350 CLS
360 PRINT"Now 'Boot' up logistix disc and type: /LC"+name$+"<return>R"
370 PRINT"Then save the data in the normal fashion."
380 VDU26:PRINT''''
390 END
400 :
410 DEFPROCgetdescriptor
420 LOCAL X,f
430 LOCAL ERROR
440 X=OPENUP(path$+".Descriptor."+name$)
450 INPUT#X,fields%,lastrec%
460 reclen%=0
470 FOR f=1 TO fields%
480 INPUT#X,ftype$,flen,fname$(f)
490 reclen%=reclen%+flen+2
500 NEXT f
510 CLOSE#X
520 ON ERROR LOCAL *CDIR $.Logistix
530 *DIR $.Logistix
540 *DIR $
550 ENDPROC
560 :
570 DEFFNstrip(f$)
580 LOCAL i:i=1
590 WHILE MID$(f$,i,1)=" " AND i<LENf$
600 i+=1:ENDWHILE
610 =MID$(f$,i)
620 :
630 DEFPROCout(T$,last)
640 LOCAL c$,i
650 FOR i=1 TO LENT$
660 c$=MID$(T$,i,1)
670 REM Replace commas and quotes (CHR$34) in fields by \ character
680 IF c$="," OR c$=CHR$34 c$="\"
690 BPUT#lgx,ASCc$
700 NEXT
710 IF NOT last BPUT#lgx,ASC","
720 ENDPROC
730 :
740 DEFFNresponse(q$,a$)
750 PRINTCHR$12;q$;">";a$'''"<return> for no change."
760 OFF:G=GET:ON
770 IF G=13 THEN =a$
780 REM Line 800 puts key pressed
790 REM back into keyboard buffer
800 SYS "OS_Byte",138,0,G
810 PRINTTAB(LENq$+1,0);SPC(80)
820 INPUTTAB(LENq$,0);">"oa$
830 IF oa$<>"" THEN a$=oa$
840 =a$
850 :
860 DEFPROCwriteheadings
870 FOR f=1 TO fields%
880 PROCout(fname$(f),f=fields%)
890 NEXT:PROCcr
900 ENDPROC
910 :
920 DEFPROCcr
930 BPUT#lgx,13:BPUT#lgx,10
940 ENDPROC
950 :
960 CLOSE#0:ON:VDU26
970 PRINTTAB(0,15);:REPORT
980 PRINT" @ line ";ERL