Here’s how to use import() function.
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:
A.export()
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:
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 |