The IMPORT Command (Read data from files)

In KSE, the IMPORT command reads data into the MAIN table in three modes:

IMPORT /XLSX   Read an Excel worksheet

IMPORT /KEG    Read a /KEG format file OR a 'flat' Text file (e.g. for .csv, .txt)

IMPORT /KBIN    Read a Kegsoft binary file (compacted / encrypted data file).

Typically, the IMPORT command is used to read data in files of up to the capacity line limits of Excel or typically up to around 500k rows of text data. Above this, it is worth considering the FILE command as it provides extra functionality and control for reading very large files.

1. IMPORT /XLSX - Loading Spreadsheets into MAIN

Usually, spreadsheets have the column headings in the first row so the default way of reading an xlsx file is:

Import /XLSX My_file Worksheet=Sheet1

Unless changed by the user, Excel normally labels a worksheet Sheet1. If the file exists in the Studio /Data folder (use System > Bring In if it is not), the data will be read into MAIN with the column headings used in the worksheet.

If the worksheet has been given another name, specify it in the worksheet= clause. the import might be:

Import /XLSX CustomerList worksheet=SME_List

Note, IMPORT will usually import the data as TEXT (it is not prescriptive about data) so you will need to define how you want to use columns other than as text i.e. INT (Integer), REAL (decimal) or DATE, for example, if you wanted to do maths or statistics on specific columns. To redefine a column's type, use the DEFINE command. E.g.

Define /COL c.2 TYPE REAL // Look up the DEFINE command for more options

If the spreadsheet does not have the column names set in the first row, you should prefix the IMPORT command with a SET command to avoid the first line of data being used as column headings

Set /Import XLSX HDRFORMAT NONE // Tell import not to use first line for headings
Import /XLSX  CustomerList worksheet=SME    // Read a headerless worksheet called SME_List

2.1 IMPORT /KEG -  Loading KEG format files into MAIN

/KEG option handles data for text formats. The file extension is always .keg.data.txt so a /KEG file called Customer_list will be called:

Customer_list.keg.data.txt

The reason for this structure is that KBL and the Kegsoft IT platform use different file types, most of which are text based so the extension used here explicitly tells us that we are dealing with text formats. Kegsoft has it's own specific and preferred data format which defines the columnar structure and field delimeters so unless told otherwise (with the SET command), the KBL processor will assume that the file is in /KEG format.

To read the /KEG format file requires the simple default form of the command. The following command will import a /KEG file:

Import /KEG CUSTOMER_list

This will import the KEG file into MAIN, it has a header section with the column definitions and field separator (delimiter), followed by the data. If you look at a /KEG  file in Notepad it will to look as follows: 

ADD /COL c.1 Customer_id INT
ADD /COL c.2 Order_value REAL
ADD /COL c.3 Customer_Name TEXT
ADD /COL c.4 Customer_type TEXT
ADD /COL c.5 Customer_email TEXT
ADD /COL c.6 Sequence INT
SEP |
DATA
1241|684.67|Tyler Engineering|SME|admin@tylereng.co.uk|1
1242|683.92|Carols Cakes|SME|cakes@ccakes.co.uk|2
1253|530.68|Steve's Plugs|SME|steve@steveplugs.co.uk|3
1252|239.86|Richard Philby|PER|philby666@yahoo.co.uk|4
1258|234|Ageism Concern|CHA|enquiries@Ageist.org.uk|5
1251|233.4|Mrs Sara Smith|PER|ssmith2@mymail.com|6
1254|210.95|Mr John Enderby|PER|streak17@hotmail.co|7
1290|199.1|John Fitch|PER|JF101@gmail.com|8
1285|194.18|Mr Lucien Semon|PER|lsemon@frpneus.fr|9

Note here, the column name, type settings and the field | separator (the SEP | line defines this). This default separator can be changed to another character if required. 

2.2 IMPORT - .csv and .txt files into MAIN

It is very common for data files provided to you, to be text based and often formatted with comma delimiters. To import text-based files, we again use IMPORT /KEG command as we are dealing with text.

Step 1

Save the .txt or .csv file you want to read into a file with the extension .keg.data.text You can do this either by copying the file with the new extension or just renaming it. So an original file named myfile.txt (or .csv) should become myfile.keg.data.txt Make sure the file is in the Studio /Data folder)

Step 2

Because we will not be using the /KEG format we need to change defaults before using the IMPORT command by prefixing with SET command(s) to override the default standard /KEG file structure

Set /IMPORT KEG HDRFORMAT LINE    //Override the normal keg format header settings
Import /KEG myfile // Import the flat file called myfile

The above, tells the KBL processor to expect a text file that is comma delimited with the first row as the column headings (like a normal .csv). A file such as the following format would be expected:

Cust_id,Ord_value,Cust_name,Cust_type,Cust_email,Sequence
1241,684.67,Tyler Engineering,SME,admin@tylereng.co.uk,1
1242,683.92,Carols Cakes,SME,cakes@ccakes.co.uk,2
1253,530.68,Steve's Plugs,SME,steve@steveplugs.co.uk,3
1252,239.86,Richard Philby,PER,philby666@yahoo.co.uk,4
1258,234,Ageism Concern,CHA,enquiries@Ageist.org.uk,5
1251,233.4,Mrs Sara Smith,PER,ssmith2@mymail.com,6

If the file contained rows of data uses a delimiter other than comma (e.g. the | concatenate marker as commonly used, say if there are commas used in customer addresses), you should additionally precede the IMPORT command with a SET command to specify the non-standard delimeter:

Set /IMPORT KEG DELIMITER |    // Here the delimiter to be used is the | character
Set /IMPORT KEG HDRFORMAT LINE    //Override the normal keg format header settings
Import /KEG TestCust // Import the text-based file called TestCust

This would import a file with the following format:

Cust_id|Ord_value|Cust_name|Cust_type|Cust_email|Sequence
1241|684.67|Tyler Engineering|SME|admin@tylereng.co.uk|1
1242|683.92|Carols Cakes|SME|cakes@ccakes.co.uk|2
1253|530.68|Steve's Plugs|SME|steve@steveplugs.co.uk|3
1252|239.86|Richard Philby|PER|philby666@yahoo.co.uk|4
1258|234|Ageism Concern|CHA|enquiries@Ageist.org.uk|5
1251|233.4|Mrs Sara Smith|PER|ssmith2@mymail.com|6

Note that the above files / formats provide no information about the column formats so they will be defaulted to TEXT in MAIN, to change type, use the DEFINE command.

3. IMPORT - /KBIN (Kegsoft binary file)

A /KBIN file is another Kegsoft-specific data file type. The data are compacted and encrypted as a binary file, thus making them small and efficient to store, They can only be read by KBL. They are created in much the same way as /KEG files and have the column names and type definitions built into them. The naming convention for /KBIN files is filename.kbin.kegsoft. Examples

Import /KBIN Myfile // Reads a Keg format-style file into MAIN except it's a binary file

4. IMPORTing more complex/non-standard file structures into MAIN

Note that importing data directly from database tables (including cloud-based) will be a part of the KBL Studio Plus command Set (e.g. SQLServer)

4.1 Bespoke flat file formats

KBL can be used to interpret bespoke and structured files that are more complex than say, standard ,csv files by the use the SPARSER command (Simple Parser) which provides programmatic control for interpreting line content (including line by line conditional logic). For even more control see the FILE command set and examples which uses a data staging area before data are imported into MAIN memory tables. This functionality covers interpreting and loading data into KBL where a file has multiple data definitions / sections.

Applications which will find the SPARSER command useful include ETL (Extract, Transform and Load) and interfacing tasks where the files may have heads, footers and intermediate markers through the file.

 4.2 IMPORTing Very Large Datasets (VLDs)

Because VLDs pose the additional challenge of high memory and processor consumption, the FILE command set is recommended. The ability to pre-process data before being loaded into the MAIN (and subsequent) table provides means of writing more memory efficient scripts. This functionality is also useful in interpreting more subtle data formats procedurally. More Information

4.3 IMPORTing a Fixed-format (fixed-length) text-based File

If the file you want read has fixed format where the fields occupy set character positions padded out with spaces, the easiest way to load them into columns is import the records as a single column character string, then split the text into separate columns. For example if the fixed-length text was as follows:

1241        Tyler Engineering    684.7 admin@tylerengineering.co.uk
1242        Carols Cakes              683.9 carol@carolscakes.co.uk

4.4 Using Parameters in IMPORT

Remember that filenames can always be parametrised (for example for entering via an INPUT form or specifying at the head of a script)

Examples

5. Creating /KEG files & converting between /XLSX (spreadsheets) and /KEG format file

To create (output) a /KEG file from data in MAIN, you simply need to use:

EXPORT /KEG Filename // Create a standard KEG format file with | delimiters

To create a /XLSX file from MAIN:

EXPORT /XLSX Filename Worksheet={worksheetName}    // Creates a standard Excel file

You can easily convert your spreadsheets to /KEG format files and vice versa using the IMPORT and EXPORT commands. For simplicity and convenience, unless you have specific need to use spreadsheets, we recommend working in /KEG format as it has the advantage of preserving the format types in the file definition and for transparency, for large or more complicated files you have the option of editing your data files in a text editor (e.g. Notepad), if it suits you.

Back to top