f. import()

f.import()

f.import(F_i:type:fmt,…;k:n,s)

Retrieve the k^th segment of the n segments from the text file. F_i represents the retrieved field; all fields will be retrieved by default. s is the user-defined separator; the default is tab. When retrieving a file segment by segment, it intelligently identifies the ending points to ensure the retrieved records in each segment are complete and all retrieved records are continuous and unique.

f

A file

F_i

Fields to be retrieved. All fields will be retrieved by default. The sign # is used to represent a field with a sequence number.

type

Field types include bool, int, long, float, decimal, string, date, time and datetime. Data type of the first row will be used by default; when the parameter value is an integer, it represents serial byte key, which allows 16 bytes only.

fmt

Date\time format.

s

User-defined separator; the default separator is tab. When the parameter is omitted, the comma preceding it can be omitted, too.

k

The segment number.

n

The number of segments. Retrieve the whole file when both k and n are omitted.

@t

Use the first row of f as the field name. If this option is not used, _1, _2,… will be used as field names; keep the original field names if F_i is #i.

@b

Retrieve data from a binary file exported in the export method, with support for parameter F_i, k and n and without support for parameters type and s. Options @t, @s, @i, @q, @a, @n, @k, @p, @f, @l, @m, @c, @o, @d, @v and @r will be ignored; do not work with @k option and @n option. The segmental retrieval could result in empty segment in cases when a file has only a very small number of records or it is unable to be segmented.

@e

Make the function return null when F_i doesn’t exist in the file; raise an error when the option is absent.

@s

Do not split the to-be-retrieved field when it is imported as a cursor whose content is a table sequence consisting of strings of a single field; in this case the parameters will be ignored.

@i

If the result set has only one column, return it as a sequence.

@q

Remove the quotation marks, if any, from both ends of data items, including the field names, and handle escape sequences; but will keep quotation marks within the data item.

@a

Treat single quotes as what they are; left not handled by default, and can work with @q@p.

@p

Enable handling the matching of parentheses (not including the separators within the parentheses) and quotes, as well as the escape sequences outside of the quotes.

@f

Split the file content into a string by the separator without parsing.

@l

Allow line continuation where there is an escape character \ at the end of the line.

@m

Use multithreads to increase data retrieval speed, but this will leave an indefinite order for members of the result set. This option will be ignored when parameters k and n exist and it is often used to retrieve data from big files. More than one parallel thread should be specified in configuration information. errors may appear when @o option or parameters k:n are present.

@c

Use comma as the separator when the parameter s is absent.

@o

Perform escaping according to the Excel rule, which identifies two double quotation marks as one and does not escape the other characters.

@k

Retain white spaces on both sides of the data item; without it, white spaces on both ends will be automatically deleted.

@d

Delete a record if it contains unmatching data types or data formats and start examining data by type, or if the parentheses and the quotation marks in it do not match when @p option and @q option respectively are present.

@n

Ignore and discard rows whose number of columns don’t match the first row.

@v

Verify data type matching when @d option and @n option are present, and, if error reports, throw an exception, terminate the execution and output the content of the problem record.

@w

Read each row, including the column headers row, as a sequence and return a sequence of sequences.

@r

Read contents as a string before parsing so that errors about some character sets can be avoided; the option slows the computation.

@y

Parse the file data as a table sequence with fixed-length fields. In this case, parameter fmt is regarded as the field’s length. When parameter type is datetime, compute field’s length using fmt.

This option does not work with @t option. With this option, parameter F_i is interpreted as a result field name; when this parameter is absent, just skip this field during parsing. Do not change the order of fields in the original file.

When @e option is present, fields for which fmt isn’t specified are regarded as the newly-added empty ones; the normal parsing will trigger an error report.

A

1

=file("D:\\score.txt").import()

2

=file("D:\\score.txt").import@t()

3

=file("D:\\score.txt").import(;1:2)

With F_i and s omitted, get the data of the first of the multiple segments.

4

=file("D:\\Department2.txt").import(;"|")

With F_i, k and n omitted, the whole file is imported.

5

=file("D:\\Department2.txt").import(;1:3,"|")

Omit the fields to be imported.

6

=file("D:\\ EMPLOYEE.txt").import@c(GENDER;1:2)

Retrieve the first segment of GENDER field of comma-seperated EMPLOYEE1.txt.

7

=file("D:\\Department5.txt").import@t(DEPT, MANAGER:int; 1:3,"/")

The contents of Department5.txt are separated with slash and retrieved by the specified fields DEPT and MANAGER.

8

=file("D:\\ score.txt").import@e(EID;1:3)

Return null because there’s no EID field in score.txt.

9

=file("D:\\Department.txt").import@ts()

10

=file("D:\\EMPLOYEE.btx").import@b(;1:2)

Retrieve the first segment of the bin file (a segmented binary file) EMPLOYEE.btx, which is exported from f.export(A,x:F,…).

11

=file("D:\\orders.txt").import@mt(;",")

Increase the speed of retrieving data from the big file. The record order in the result is not the same as that in the file.

12

=file("D:\\StuName.txt").import@i()

StuName.txt has only one column, so return it as a sequence.

13

=file("D:\\test.txt").import@t()

Certain field names and field values are quoted.

14

=file("D:\\test.txt").import@tq()

With @q option, quotation marks at both ends of each data item (including each field name) are removed without handling those within.

15

=file("D:\\Sale1.txt").import()

Get all records from Sale1.txt.

16

=file("D:\\ Sale1.txt").import(#1,#3)

Get the first column and the third column from Sale1.txt.

17

=file("D:/Dep3.txt").import@cqo()

Here is the Dep3.txt file:

With @o option, two double quotation marks are treated as one and return the result as follows:

18

=file("D:/Dep1.txt").import@k()

Retain the whitespaces on both sides of the data item.

19

=file("D:/Department1.txt").import@t(id:int,name;,"|")

20

=file("D:/Department1.txt").import@td(id:int,name;,"|")

Delete the record as it contains unmatching data types.

21

=file("D:/Department1.txt").import@tdv(id:int,name;,"|")

Check data type matching, and, if error reports, throw an exception, terminate the execution and output the content of the problem record.

22

=file("Dep2.txt").import@tdn(id:int,name,surname;,"|")

Here’s the file Dep2.txt:

Ignore and discard row 6 and row 8 because the number of columns don’t match that of the first row.

23

=file("D://EMP1.txt").import@s(;1:2)

Don’t split field values to import as a single-field table; ignore the parameters.

24

=file("D://EMP2.txt").import(#2:date:"yyyy/MM/dd")

EMP2txt:

Parse data in yyyy/MM/dd format as a date type field.

25

=file("City.txt").import@w()

Use @w option to return a sequence of sequences.

26

=file("D://t1.txt").import@c()

Below is file t1.txt:

With @c option, use the comma as the separator by default and return result as follows:

27

=file("D://t1.txt").import@cp()

With @p option, parentheses and quotation marks matching will be handled during parsing.

28

=file("D://t1.txt").import@cpa()

With @a option, single quotation marks are identified as quotation marks.

29

=file("D://t2.txt").import@l()

Below is file t2.txt:

With @1 option, allow line continuation when there is an escape character at the end of the line.

30

=file("D://t3.txt").import@f()

With @f option, just split the file as a string using the separator.

A

1

=file("um.txt")

2

=A1.import()

Read data from um.txt and return the following content:

3

=A1.import@y(UID:int:1,Amount:int:5,Udate:string:18)

@y option enables a fixed-length parsing. The 1^st column is named UID,  its length is 1 and its type is int; the 2^nd column is named Amount, its length is 18 and its type is string. The result is as follows:

4

=A1.import@y(UID:int:1,Amount:int:5,Udate:datetime:"yyyy-MM-ddHH:mm:ss")

The 3^rd column is datetime type, according to which its length is computed. The result is as follows:

5

=A1.import@ye(UID:int:1,UName::8,Amount:int:5,Udate:string:18)

With @e option, in the 2^nd column is regarded as a newly-added empty one as its parameter fmt is absent. The result is as follows:

T

An entity table

x

An expression

C

A column name

w_i

Filtering condition; retrieve the whole set when this parameter is absent; separate multiple conditions by comma(s) and their relationships are AND. Besides regular filtering expressions, you can also use the following five types of syntax in a filtering condition, where K is a field in the entity table:

1．K=w

w usually uses expression T_i.find(K) or T_i.pfind(K), where T_i is a table sequence. When value of w is null or false, the corresponding record in the entity table will be filtered away; when w is expression T_i.find(K) and the to-be-selected fields C,... contain K, T_i’s referencing field will be assigned to K; when w is expression T_i.pfind(K) and the to-be-selected fields C,... contain K, sequence numbers of K values in T_i will be assigned to K.

2．(K₁=w₁,…K_i=w_i,w)

K_i=w_i is an assignment expression. Generally, parameter w_i can use expression T_i.find(K_i) or T_i.pfind(K), where T_i is a table sequence; when w_i is expression T_i.find(K_i) and the to-be-selected fields C,... contain K_i, T_i’s referencing field will be assigned to K_i correspondingly; when w_i is expression T_i.pfind(K_i) and the to-be-selected fields C,... contain K_i, sequence numbers of K_i values in T_i will be assigned to K_i.

w is filter expression; you can reference K_i in w.

3．K:T_i

T_i is a table sequence. Compare K_i value in the entity table with key values of T_i and discard records whose K_i value does not match; when the to-be-selected fields C,... contain K, T_i’s referencing field will be assigned to K.

4．K:T_i:null

Filter away all records that satisfy K:T_i.

5．K:T_i:#

Locate records according to sequence numbers, compare sequence numbers of records in table sequence T_i according to the entity table’s K values, and discard non-matching records; when the to-be-selected fields C,... contain K, T_i’s referencing field will be assigned to K

@v

Enable generating a pure table sequence

@x

Automatically close the entity table after data is fetched from the cursor

A

1

for 100

2

=to(10000).new(#:k1,rand():c1).sort@o(k1)

Generate a set of random data.

3

=to(10000).new(#:k1,rand(10000):c2,rand()*1000:c3).sort@o(k1)

4

=A2.cursor()

5

=A3.cursor()

6

=file("D:\\tb1.ctx")

Create composite table’s base table.

7

=A6.create(#k1,c1)

8

=A7.append(A4)

9

=A7.attach(table4,c2,c3)

10

=A9.append(A5)

11

=A9.cursor(;c2<1000;2:3)

Divide records in A9’s attached table where c2 is less than 1000 into 3 segments and return columns in the 2nd segment as a cursor.

12

=A11.fetch()

Fetch data from A11’s cursor.

13

=A9.import@x(;c2<1000;2:3)

Same result as A12; automatically close A9’s entity table after records are retrieved.

A

1

=file("emp.ctx")

2

=A1.open()

Open the composite table file.

3

=A2.import()

As no parameters are present, return all data in the entity table.

4

=5.new(~:ID,~*~:Num).keys(ID)

Generate a table sequence using ID as the key:

5

=A2.import(EID,NAME;EID=A4.find(EID))

Use filter mode K=w; in this case w is T_i.find(K) and entity table records making EID=A4.find(EID) get null or false are discarded; EID is the selected field, to which table sequence A4’s referencing field is assigned.

6

=A2.import(EID,NAME;EID=A4.pfind(EID))

Use filter mode K=w; in this case w is T_i.pfind(K) and entity table records making EID=A4.pfind(EID) get null or false are discarded; EID is the selected field, to which its sequence numbers in table sequence A4 are assigned.

7

=A2.import(EID,NAME;EID:A4)

Use filter mode K:T_i; compare the entity table’s EID values with the table sequence’s key values and discard entity table records that cannot match.

8

=A2.import(NAME,SALARY;EID:A4)

This is a case where K isn’t selected; EID isn’t the selected field, so only filtering is performed.

9

=A2.import(EID,NAME;EID:A4:null)

Use filter mode K:T_i:null; compare the entity table’s EID values with the table sequence’s key values and discard entity table records that can match.

10

=A2.import(EID,NAME;EID:A4:#)

Use filter mode K:T_i:#; compare with sequence numbers of table sequence’s records according to the entity table’s EID values, and discard records that cannot match.

11

=connect("demo").query("select top 2  NAME,GENDER  from employee").keys(NAME)

Return a table sequence using NAME as the key:

12

=A2.import(EID,NAME;(EID=A4.find(EID),NAME=A11.find(NAME),EID!=null&&NAME!=null))

Use filter mode (K₁=w₁,…K_i=w_i,w); return records that meet all conditions.