Since an internal file of Decimal BASIC is a CSV file, data cooperation with a spreadsheet software is possible.
We must know the correspondence between the position in a CSV file and the position of the cell.
A line in a CSV file corresponds to a row in the spreadsheet.
In Decimal BASIC, one line in a CSV file correspond to one execution of a READ statement or a WRITE statement.
That is, Items in a READ or WRITE statement corresponds to a row in a spreadsheet.
Assume that we manage names, addresses, weights, heights with spreadsheet software, and saved its data as "A:data.CSV" in the CSV format.
These data can be load to arrays as follows.
100 DIM a$(1000),b$(1000),c(1000),d(1000) 110 OPEN #1:NAME "A:DATA.CSV", ACCESS INPUT , RECTYPE INTERNAL 120 LET n=1 130 DO 140 READ #1,IF MISSING THEN EXIT DO: a$(n),b$(n),c(n),d(n) 150 LET n=n+1 160 LOOP 170 CLOSE #1
Array declarations in 100-line corresponds to items in spreadsheet. Upper bounds should be written as not to exceed the actual number of persons.
Indexed variables that corresponds to the items in the spreadsheet are named in a READ statement.
When the line 170 finishes, the number of items of data shall be assigned to the variable n, data read into the arrays.
When we deal with data for every line, we write a program such as follows.
110 OPEN #1:NAME "A:DATA.CSV", ACCESS INPUT , RECTYPE INTERNAL 120 DO 130 READ #1,IF MISSING THEN EXIT DO: a$,b$,c,d 140 PRINT a$,b$,c,d 160 LOOP 170 CLOSE #1
When we write out data generated by BASIC into a CSV file instead of the display, we open a file as an internal file, and use WRITE statements instead of PRINT statements as follows.
110 OPEN #1:NAME "A:DATA.CSV", RECTYPE INTERNAL 120 ERASE #1 130 FOR x=0 TO 10 140 WRITE #1: x, SQR(x) 150 NEXT x 160 CLOSE #1 170 END
If the resulting file is read into a spreadsheet, the first column shall be numbers from 1 to 10, the second column the corresponding square roots.
When we pass the data that are contained in the first to the n-th elements of arrays a$,b$,c,d using the program as follows.
110 OPEN #1:NAME "A:DATA.CSV", RECTYPE INTERNAL 120 ERASE #1 130 FOR i=1 TO n 140 WRITE #1: a$(i),b$(i),c(i),d(i) 150 NEXT i 160 CLOSE #1
When the data is read into the a spreadsheet, the portrait orientation corresponds to the indices of arrays and the landscape orientation A$，B$，C，D.
An execution of a MAT WRITE statement for an internal file outputs a line of data.
Thus, when it is read into a spreadsheet, data shall be arranged in a row.
We write matrix data stored in a 2-dim array so that they can be read into a spreadsheet as follows.
Ex.1 Use an internal file and a WRITE statement.
100 DIM a(2,3) 110 DATA 1,2,3 120 DATA 4,5,6 130 MAT READ a 140 OPEN #1: NAME "a:data.csv", RECTYPE INTERNAL 150 ERASE #1 160 FOR i=1 TO 2 170 WRITE #1:a(i,1),a(i,2),a(i,3) 180 NEXT i 190 CLOSE #1 200 END
Ex.2 Use a plain text and write out commas explicitly.
100 DIM a(2,3) 110 DATA 1,2,3 120 DATA 4,5,6 130 MAT READ a 140 OPEN #1: NAME "a:data.csv" 150 ERASE #1 160 FOR i=1 TO 2 170 FOR j=1 TO 3 180 PRINT #1:a(i,j); 190 IF j<3 THEN PRINT #1:","; ELSE PRINT #1 200 NEXT j 210 NEXT i 220 CLOSE #1 230 END
Ex.3 Use RECTYPE CSV, one of original enhancements.
100 DIM a(2,3) 110 DATA 1,2,3 120 DATA 4,5,6 130 MAT READ a 140 OPEN #1: NAME "a:data.csv", RECTYPE CSV 150 ERASE #1 160 MAT WRITE #1: a 170 CLOSE #1 180 END
How we read matrix data written out by a spreadsheet in a CSV format using MAT READ ?
Ex.4 Read line by line.
10 DIM a(2,3) 20 OPEN #1: NAME "a:data.csv" 30 FOR i=1 TO 2 40 INPUT #1:a(i,1),a(i,2),a(i,3) 50 NEXT i 60 CLOSE #1 70 MAT PRINT a 80 END
Ex.5 Use RECTYPE CSV
10 DIM a(2,3) 20 OPEN #1: NAME "a:data.csv", RECTYPE CSV 30 MAT READ #1:a 40 CLOSE #1 50 MAT PRINT a 60 END