import()

Read(273) Label: import,

Here’s how to use import() function.

S .import()

Description:

Retrieve contents from strings as records and return them as a table sequence.

Syntax:

S.import(Fi:type;fmt,…;s)

 

Note:  

The function retrieves specified or all fields from string S and returns them as a table sequence.

Parameters:

S

The string. Its format: separate the records by line break, and the fields by user-defined separator; the default separator is tab.

Fi

Fields to be retrieved; by default all fields will be retrieved.

type

Field types include bool, int, long, float, decimal, number, string, date, time and datetime; data type of the first row will be used by default. It is the length of a serial byte when it is an integer.

fmt

Date\time format

s

User-defined separator; the default is tab.

Options:  

@t

Take the first row in f as the field name. If not using this option, then use _1, and _2,… as the field name.

@c

Use comma as the separator when the parameter s is absent, but the user-defined separator s should take priority when there are both s and @c option.

@j

Import records from json strings and resolve them into a table sequence, with s being ignored. Of [{F:v,…},…], v is the value of F; it will be quoted when being the string constant, and represented reclusively when it is a sequence or a table sequence.

@x

Retrieve data from strings of XML format as multi-level records or a table sequence (similar to retrieving data from json strings).

The identifier within <> is the field name; identifiers with the same name indicate that a table sequence will be generated; ignore the non-identifier properties within <>.

<xml>

<table>

        <row>

          <F>v</F>

         

        </row>

       

 </table>

</xml>

s is the level separator; slash / is used to separate the level of data to be retrieved and its superior level; when omitted data will be retrieved from the root level.

@s

Won’t split strings and data will be imported as a table sequence consisting of strings of single field values; ignor the parameters

@i

Return the result set as a sequence if it only contains one field

@q

First remove quotation marks surrounding strings, including the headers, and then handle the escaping

@o

Use quotation marks as the escape character

@k

Retain the whitespaces on both sides of the data item; without it a trim operation will be automatically performed

@e

Generate null if parameter Fi isn’t included in the imported strings; by default there will be an error report

@d

Delete a record if it contains unmatching data types or data formats and start examining data by type

@n

Ignore a row whose number of columns don’t match the first row

@v

In corporation with @d or @n, if a mismatch appears, throw an exception, terminate the execution and output the content of the problem record

Return value:  

Table sequence

Example:  

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=A1.(~.array().concat@c())

Convert to the sequence of strings.

 

3

=A2(1).import(;",")

Select all fields from a specified string in the sequence. Specify comma as the separator, and return a table sequence as the result

4

=demo.query("select EID,NAME,SURNAME from EMPLOYEE")

 

5

=A4.export()

6

=A4.export@t(EID:id,NAME:name,SURNAME:surname;"|")

7

=A5.import()

No parameters are given. The default separator will be tab, and _1 and _2,… will be used as field names

8

=A6.import@t(id:int,name;"|")

Select fields id and name, separated by “|”

9

="{name:'China',province:[{name:'Heilongjiang',cities:

{city:['Harbin','Daqing']}},{name:'Guangdong',

cities:{city:['Guangzhou','Shenzhen','Zhuhai']}},{name:'Taiwan',cities:

{city:['Taipei','Kaohsiung']}},{name:'Xinjiang',

cities:{city:['Urumqi']}}]}".import@j()

Parse JSON strings to get a table sequence - [{F:v,},], where v is the value of F field; write v recursively if it is a sequence or table sequence

10

=A4.export@j()

11

=A10.import@j()

12

=A4.export@x(;"employee/name")

13

=A12.import@x(EID;"employee/name")

Retrieve EID field of the name level.

14

=A2(1).import@c()

With @c option, use default separator commma

15

=A5.import@s()

 

With @s option, won’t split strings and records are imported as a single-field table sequence

16

=A5.import@si()

 

A single-field result set will be returned as a sequence

17

=["12\r34","aa\nbb"].export@q()

18

=A17.import()

19

=A17.import@q()

 

With @q option, double quotations will be removed before generating the final table sequence

20

=["12\r34","aa\nbb"].export@qo()

21

=A20.import@qo()

 

With @o option, use double quotations as escape character

22

=" abc ".import@k()

Retain the whitespaces on boths sides

23

=A6.import@te(id:int,name,dept;"|")

Generate null since dept doesn’t exist in the imported strings

24

id|name|surname

a|Rebecca|Moore

2|Ashley|Wilson

3|Rachel|Johnson

4|Emily|Smith

5|Ashley|Smith

6|Matthew|Johnson

7|Alexis|Smith

8|Megan|Wilson

 

25

=A24.import@td(id:int,name;"|")

Delete the record as it contains unmatching data types

26

=A24.import@tv(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; the error message is: Error in cell A26

 

27

id|name|surname

1|Rebecca|Moore

2|Ashley

3|Rachel|Johnson

4|Emily

 

28

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

Ignore row 2 and row 3 because the number of columns don’t match that of the table sequence

Related functions:

f.export()

f.import()

A.export()

f .import()

Description:

Read contents from a file and return them as a table sequence.

Syntax:

f.import()

 

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

Retrieve the kth segment of the n segments from the text file. Fi 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

Note:

The function retrieves file f and returns a table sequence where each record consists of a line of f.

Parameters:

f

A file

Fi

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. It is the length of a serial byte when it is an integer.

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.

Options:

@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 Fi is #i

@b

Retrieve data from the exported binary file, with the support for parameter Fi, k and n, and with no support available for parameters type and s. Options @t, @s, @i, @q and @m will be ignored. 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 Fi 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, of the field strings, including the field names, in the first place, and handle the escape sequences.

@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 license file and configuration information.

@c

Use comma as the seperator when the parameter s is absent, but the user-defined separator s should take priority when there are both s and @c option.

@o

Use quotation marks as the escape character

@k

Retain the whitespaces on both sides of the data item; without it a trim operation will be automatically performed

@d

Delete a record if it contains unmatching data types or data formats and start examining data by type

@n

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

@v

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

Return value:

The new table sequence whose records are contents of the file object f.

Example:

 

A

 

1

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

2

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

3

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

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

4

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

With Fi, 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()

 

14

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

 

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@qo()

Here is the Dep3.txt file:

Return the result:

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@tv(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(#1:date:"yyyy/MM/dd")

EMP2txt:

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

Note:

Text file format: Separate records by carriage return, and fields by the user-defined separator. The default separator is the tab.

Related functions:

f.export()

 

T.import()

Description:

Read in records from an entity table and return them as a sequence, record sequence or table sequence.

Syntax:

T.import(...)

Remark:

The function, which is equivalent to T.cursor(...).fetch(), retrieves records from entity table T and returns them as a sequence, record sequence or table sequence.

Parameters:

T

An entity table

Return value:

A sequence/record sequence/table sequence

Example:

 

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;k1)

 

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(;c2<1000;2:3)

Same result as A12