Calcpad - Online help - Import/export of external data

This site uses cookies to offer you a better browsing experience. Learn More.

Topic: Programming

Online Help

Import/export of external data

You can import and export numerical data from/to text, CSV and Excel files. Inside Calcpad, data should be stored in a matrix/vector variable. You can also read and write partial data by specifying the desired range. Structured storage of special matrices is supported for saving space. The following commands are available:

Text/CSV files

#read M from filename.txt@R1C1:R2C2 TYPE=R SEP=',' - reads data from the specified text/CSV file into the matrix/vector M. The file must exist;

#write M to filename.txt@R1C1:R2C2 TYPE=N SEP=',' - writes data from matrix/vector M to the specified text/CSV file. If the file exists, it is entirely overwritten. Otherwise, a new file is created. In all cases, the path to the file must exist;

#append M to filename.txt@R1C1:R2C2 TYPE=N SEP=',' - appends data from matrix/vector M to the specified text/CSV file. If the file exists, the data is appended at the end of the existing file. Otherwise, a new file is created. In all cases, the path to the file must exist.

Command options

M - the name of the matrix/vector that contains the data [required];
filename.txt - the name and path of the input/output file [required]. If the path is omitted, the file is assumed to be in the same folder. Extension is required. Any valid extension is allowed (except those for Excel), including txt and csv, as long as the data in the file is in text format;
@R1C1:R2C2 - data range in the input file [optional]:
R1C1 - starting row and column indexes [optional]:
R1 - row index: includes capital letter “R”, followed by the number of the row [optional];
C1 - column index: capital letter “C”, followed by the number of the column [optional];
:R2C2 - ending row (R2) and column (C2) indexes as above [optional];
Indexing starts at 1. You can skip any of the starting and ending row/column indexes. In this case, the default values of 1 and matrix dimensions are taken. Starting indexes can be greater than ending ones. Some examples are given below:

	C1	C2	C3	C4	C5
R1	11	12	13	14	15
R2	21	22	23	24	25
R3	31	32	33	34	35
R4	41	42	43	44	45
R5	51	52	53	54	55

	C1	C2	C3	C4	C5
R1	11	12	13	14	15
R2	21	22	23	24	25
R3	31	32	33	34	35
R4	41	42	43	44	45
R5	51	52	53	54	55

	C1	C2	C3	C4	C5
R1	11	12	13	14	15
R2	21	22	23	24	25
R3	31	32	33	34	35
R4	41	42	43	44	45
R5	51	52	53	54	55

	C1	C2	C3	C4	C5
R1	11	12	13	14	15
R2	21	22	23	24	25
R3	31	32	33	34	35
R4	41	42	43	44	45
R5	51	52	53	54	55

@R2C3:R4C4 @R2C3 @:R3C4 @R2:R4

	C1	C2	C3	C4	C5
R1	11	12	13	14	15
R2	21	22	23	24	25
R3	31	32	33	34	35
R4	41	42	43	44	45
R5	51	52	53	54	55

	C1	C2	C3	C4	C5
R1	11	12	13	14	15
R2	21	22	23	24	25
R3	31	32	33	34	35
R4	41	42	43	44	45
R5	51	52	53	54	55

	C1	C2	C3	C4	C5
R1	11	12	13	14	15
R2	21	22	23	24	25
R3	31	32	33	34	35
R4	41	42	43	44	45
R5	51	52	53	54	55

	C1	C2	C3	C4	C5
R1	11	12	13	14	15
R2	21	22	23	24	25
R3	31	32	33	34	35
R4	41	42	43	44	45
R5	51	52	53	54	55

@C3:C4 @R3:C4 @C3 @R3C4:R1C2

TYPE=R - The type of matrix/vector for structured storage [optional].
For the #read command, TYPE can be any of the following capital letters:
R - rectangular matrix (default);
C - column matrix;
D - diagonal matrix;
S - symmetric skyline matrix;
L - lower triangular matrix;
U - upper triangular matrix;
V - vector;

For column and diagonal matrices values can be stored either on a single line or in a column of one value per line. For diagonal matrices only the values along the main diagonal are stored, for lower triangular - only below the main diagonal, and for symmetric and upper triangular - only above the main diagonal. Vector values can be spread along multiple lines, but all are collected in a single vector consequently line-by-line. Examples for structured storage are provided below:

Diagonal matrix (D)

Symmetric matrix (S)

Vector (V)

File

1	2	3	4	5

➔

Matrix

1	0	0	0	0
0	2	0	0	0
0	0	3	0	0
0	0	0	4	0
0	0	0	0	5

File

1	2
3	4	5
6	7
8	9
10

➔

Matrix

1	2	0	0	0
2	3	4	5	0
0	4	6	7	0
0	5	7	8	9
0	0	0	9	10

File

1	2	3
4	5
6	7	8
9

➔

Vector

1	2	3	4	5	6	7	8	9

For the #write and #append commands TYPE can be one of the capital letters below:
Y - Yes, the matrix structure is used;
N - No, the matrix structure is not used (default);

If “N” is selected, all matrices are stored as rectangular, regardless their type and internal structure.
All elements after the last nonzero value on the row are skipped.
SEP=',' - separator [optional]. You must specify a single character in quotes.
For the #read command it must correspond to the actual separator used in the input file.
The minimal allowed syntax for the above commands if all optional keywords are skipped is:
“#read M from filename.txt” or
“#write M to filename.txt” or
“#append M to filename.txt”.

Excel files

#read M from filename.xlsx@Sheet1!A1:B2 TYPE=R - reads the data from the specified Excel file into the matrix/vector M. The file must exist as well as the specified worksheet;

#write M to filename.xlsx@Sheet1!A1:B2 TYPE=N - writes data from matrix/vector M to the specified Excel file. A new file with a single worksheet is created. If the file exists, it is entirely overwritten. The path to the file must exist;

#append M to filename.xlsx@Sheet1!A1:B2 TYPE=N - appends data from matrix/vector M to the specified Excel file. If the file exists, the data is written in the existing file at the specified location. Otherwise, a new file is created. In all cases, the path to the file must exist.

Command options

M - the name of the matrix/vector that contains the data [required];
filename.xlsx - the name and path of the input/output file [required]. If the path is missing, the file is assumed to be in the same folder. The supported extensions are xlsx and xlsm;
@Sheet1 - the name of the target worksheet [optional]. If omitted the first worksheet is used for existing files and Sheet1 is assumed for newly created ones.
!A1:B2 - target cell range [optional]:
A1 - starting cell reference [optional], where A is the column name and 1 is the row index;
:B2 - ending cell reference as above [optional];
Column names start at A, and row numbers start at 1. You can skip any of the starting and ending column/row references. In this case, data is read to the first and last nonempty cells, respectively. The starting cell references can be greater than the ending ones. Examples for data import settings are provided below:

	A	B	C	D	E
1	11	12	13	14	15
2	21	22	23	24	25
3	31	32	33	34	35
4	41	42	43	44	45
5	51	52	53	54	55

	A	B	C	D	E
1	11	12	13	14	15
2	21	22	23	24	25
3	31	32	33	34	35
4	41	42	43	44	45
5	51	52	53	54	55

	A	B	C	D	E
1	11	12	13	14	15
2	21	22	23	24	25
3	31	32	33	34	35
4	41	42	43	44	45
5	51	52	53	54	55

	A	B	C	D	E
1	11	12	13	14	15
2	21	22	23	24	25
3	31	32	33	34	35
4	41	42	43	44	45
5	51	52	53	54	55

!C2:D4 !C2 !:D3 !2:4

	A	B	C	D	E
1	11	12	13	14	15
2	21	22	23	24	25
3	31	32	33	34	35
4	41	42	43	44	45
5	51	52	53	54	55

	A	B	C	D	E
1	11	12	13	14	15
2	21	22	23	24	25
3	31	32	33	34	35
4	41	42	43	44	45
5	51	52	53	54	55

	A	B	C	D	E
1	11	12	13	14	15
2	21	22	23	24	25
3	31	32	33	34	35
4	41	42	43	44	45
5	51	52	53	54	55

!C:D !3:D !C

The behavior of data export commands #write and #append is a bit different. The starting reference indicates the location of the first element M_1,1 of the output matrix. So, even if it is greater than A1, it will not truncate the first rows and columns. Unless bound by the ending reference, the entire matrix will be written after the specified location. Otherwise, the remaining rows and columns after the ending reference will be truncated. For example: #write M to filename.xlsx@Sheet1!C2 will produce the following output:

	C	D	E	F	G
1
2	11	12	13	14	15
3	21	22	23	24	25
4	31	32	33	34	35
5	41	42	43	44	45
6	51	52	53	54	55
7

TYPE=R - type of matrix/vector for structured storage [optional]. The same rules apply as for text/CSV files above.

Import/export of external data

Text/CSV files

Excel files

Table of contents