CreateTableFromFile

CreateTableFromFile is the method added to script by the Delimited File Importer which is accessed via the following menu options:

Script Editor | Import Data | Delimited File
Project | Quick Load | Add Table From File
Project | Quick Load | Create Project From File

It is strongly recommended to use the Delimited File Importer to create the JSON for CreateTableFromFile.

Key	Value(s)	Description
project	active project name
method	CreateTableFromFile	Creates a table object and loads data from file
action	LOAD/APPEND	LOAD - Creates new table object APPEND - Appends data to an existing table object.
table	Name of table to create	Table must not already exist.
filename	File(s) containing source data	See fileFormat for supported data formats Common file types: .csv - comma separated .tsv - tab separated .txt - delimited NOTE: To load data from excel, use CreateTableFromWorkbook Loading from multiple files* To load all files in a folder into the same table, use the following syntax: *"filename": "%DATAPATH%projectdatafolder/data/"** where %DATAPATH%projectdatafolder/data is the path to the folder containing the data files NOTE: All files must contain data in the same format or the load will fail. NOTE: When loading multiple files, ensure that Data Types are correctly defined, especially if the Delimited File Importer was used to create the original CreateTableFromFile JSON as Data Types can switch from DISCRETE to CONTINUOUS as the volume of data loaded increases.
definition	FieldName \| DataType \| FieldType \| DataSize	Each field is listed as a separate value Only include the fields which are to be imported IMPORTANT: The definition list must correspond to the loading list. An error will occur if a FieldName is included in the definition section but is not included in the loading section. It is recommended to match the order of fields in the definition and loading sections - the Delimited File Importer will automatically produce JSON that is correctly ordered and can be used to create a basic method that can then be edited in other tools. CreateTableFromFile must contain either definition + loading, or definitionFile.
loading	FieldName	Each field is listed as a separate value Fields which are not to be imported (i.e., skipped) are listed as "" but must still be included in the loading section The order of fields in the loading section must match the order of fields in the data file IMPORTANT: The number of entries in the loading section may not match the number of entries in the definition section as the definition section will not contain entries for skipped fields. The loading section is not required when using definitionFile.
definitionFile	Path/Filename.xlsx	Path and filename to excel file which contains the table definition. The file must have the following layout: Name - Required. FieldName Type - Required. DataFieldType (e.g., INTEGER DISCRETE - see Data Types for more information) Length - Required. Can be blank for one, some or all fields Format - Required. Can be blank for one, some or all fields The definition file may also have additional columns which can be loaded as metadata. See Getting Started - Categories and Metadata for more information. Alternatively different files can be used to create the table and then apply metadata.
sample	true/false	If "Sample" has been checked in script editor then this will be set to true. If "Sample" is not checked in script editor, a value of true for this key will be ignored.
preHeaderSkip	0/1	TODO
dateFormat	YYYY-MM-DD MM-DD-YYYY DD-MM-YYYY YYYYMMDD MMDDYYYY DDMMYYYY M/D/Y D/M/Y	Default format = YYYY-MM-DD DataJet will recognise any separator in Date or DateTime fields, so long as the separator used is consistent. For example, DD-MM-YYYY is a valid format for DD/MM/YYYY, DD MM YYYY, DD\|MM\|YYYY etc
fileFormat	ASC118 UTF16ASUTF8
skipFirstLine	true/false	true - file does not contain headers, skip first line false - file does contains a header, don't skip first line
delimiter	, ; \| :	Any consistent file delimiter is supported, including tab, spaces etc
stripCharacter	Character to remove	Any and all instances of the character will be removed from the data as it is loaded
dateTimeFormat	YYYY-MM-DD HH:MM:SS DD-MM-YYYY HH:MM:SS YYYY-MM-DD HH:MM DD-MM-YYYY HH:MM DDMMMYYYY HH:MM:SS	Default format = YYYY-MM-DD HH:MM:SS DataJet will recognise any separator in Date or DateTime fields, so long as the separator used is consistent. For example, DD-MM-YYYY HH-MM is a valid format for DD/MM/YYYY HH:MM
trimTrailingSpaces	true/false	If true, removes any trailing spaces from any data that is loaded. For example "DATA " becomes "DATA" "0923 SHRT " becomes "0923 SHRT"
fieldQualifier	"NONE"	Specifies the text qualifier used in the file. TODO: How to specifier double quote as qualifier?
autoScanResetIfNew	true/false	Reset if new project - TODO: What does this do?
allowOverflow	true/false	If true, if a string of specified length overflows then the field will be switched to variable length string.
filenameField	"nameoffield"	The field in which to store the name of the file from which the data was loaded. NOTE: If loading data from multiple files using *"filename": "%DATAPATH%projectdatafolder/data/"** format, and there are many files in the folder, loading a sample of the data may take some time as sample data will be taken from each source file.
ignoreExisting	true/false	If true, and the specified table name already exists, the method will be skipped.

Key

Value(s)

Description

project

active project name

method

CreateTableFromFile

Creates a table object and loads data from file

action

LOAD/APPEND

LOAD - Creates new table object
APPEND - Appends data to an existing table object.

table

Name of table to create

Table must not already exist.

filename

File(s) containing source data

See fileFormat for supported data formats

Common file types:

*.csv - comma separated
*.tsv - tab separated
*.txt - delimited

NOTE: To load data from excel, use CreateTableFromWorkbook

Loading from multiple files
To load all files in a folder into the same table, use the following syntax:
"filename": "%DATAPATH%projectdatafolder/data/*"
where
%DATAPATH%projectdatafolder/data is the path to the folder containing the data files
NOTE: All files must contain data in the same format or the load will fail.
NOTE: When loading multiple files, ensure that Data Types are correctly defined, especially if the Delimited File Importer was used to create the original CreateTableFromFile JSON as Data Types can switch from DISCRETE to CONTINUOUS as the volume of data loaded increases.

definition

FieldName | DataType | FieldType | DataSize

Each field is listed as a separate value
Only include the fields which are to be imported

IMPORTANT: The definition list must correspond to the loading list. An error will occur if a FieldName is included in the definition section but is not included in the loading section. It is recommended to match the order of fields in the definition and loading sections - the Delimited File Importer will automatically produce JSON that is correctly ordered and can be used to create a basic method that can then be edited in other tools.

CreateTableFromFile must contain either definition + loading, or definitionFile.

FieldName

Each field is listed as a separate value
Fields which are not to be imported (i.e., skipped) are listed as "" but must still be included in the loading section
The order of fields in the loading section must match the order of fields in the data file

IMPORTANT: The number of entries in the loading section may not match the number of entries in the definition section as the definition section will not contain entries for skipped fields.

The loading section is not required when using definitionFile.

definitionFile

Path/Filename.xlsx

Path and filename to excel file which contains the table definition.

The file must have the following layout:

Name - Required. FieldName
Type - Required. DataFieldType (e.g., INTEGER DISCRETE - see Data Types for more information)
Length - Required. Can be blank for one, some or all fields
Format - Required. Can be blank for one, some or all fields

The definition file may also have additional columns which can be loaded as metadata. See Getting Started - Categories and Metadata for more information. Alternatively different files can be used to create the table and then apply metadata.

sample

true/false

If "Sample" has been checked in script editor then this will be set to true. If "Sample" is not checked in script editor, a value of true for this key will be ignored.

preHeaderSkip

0/1

TODO

dateFormat

YYYY-MM-DD
MM-DD-YYYY
DD-MM-YYYY
YYYYMMDD
MMDDYYYY
DDMMYYYY
M/D/Y
D/M/Y

Default format = YYYY-MM-DD

DataJet will recognise any separator in Date or DateTime fields, so long as the separator used is consistent.

For example, DD-MM-YYYY is a valid format for DD/MM/YYYY, DD MM YYYY, DD|MM|YYYY etc

fileFormat

ASC118
UTF16ASUTF8

skipFirstLine

true/false

true - file does not contain headers, skip first line
false - file does contains a header, don't skip first line

delimiter

, ; | :

Any consistent file delimiter is supported, including tab, spaces etc

stripCharacter

Character to remove

Any and all instances of the character will be removed from the data as it is loaded

dateTimeFormat

YYYY-MM-DD HH:MM:SS
DD-MM-YYYY HH:MM:SS
YYYY-MM-DD HH:MM
DD-MM-YYYY HH:MM
DDMMMYYYY HH:MM:SS

Default format = YYYY-MM-DD HH:MM:SS

DataJet will recognise any separator in Date or DateTime fields, so long as the separator used is consistent.

For example, DD-MM-YYYY HH-MM is a valid format for DD/MM/YYYY HH:MM

trimTrailingSpaces

true/false

If true, removes any trailing spaces from any data that is loaded.
For example
"DATA " becomes "DATA"
"0923 SHRT " becomes "0923 SHRT"

fieldQualifier

"NONE"

Specifies the text qualifier used in the file.
TODO: How to specifier double quote as qualifier?

autoScanResetIfNew

true/false

Reset if new project - TODO: What does this do?

allowOverflow

true/false

If true, if a string of specified length overflows then the field will be switched to variable length string.

filenameField

"nameoffield"

The field in which to store the name of the file from which the data was loaded.
NOTE: If loading data from multiple files using
"filename": "%DATAPATH%projectdatafolder/data/*"
format, and there are many files in the folder, loading a sample of the data may take some time as sample data will be taken from each source file.

ignoreExisting

true/false

If true, and the specified table name already exists, the method will be skipped.

Item	Values	Description
Description	Any string	Text to display in Script Editor Detail column
Select File	Brings up a Remote Data Source dialog	The Remote Data Source Dialog gives access to all remote files that have been uploaded to the DataJet File Server. NOTE: To add files as a remote data source, see Admin \| Remote Files \| Remote File Manager NOTE: Hold SHIFT to bring up file system on client machine. This is only valid if DataJet client and server are running on the same machine.
Convert To Load	Yes/No	TODO: What does this do?
Convert To Update	Yes/No	TODO: What does this do?
Delimiter	TAB COMMA SEMI-COLON PIPE HSTART	TODO - What is HSTART?
Date Format	YYYY-MM-DD MM-DD-YYYY DD-MM-YYYY YYYYMMDD MMDDYYYY DDMMYYYY M/D/Y D/M/Y
DateTime Format	YYYY-MM-DD HH:MM:SS DD-MM-YYYY HH:MM:SS YYYY-MM-DD HH:MM DD-MM-YYYY HH:MM DDMMMYYYY HH:MM:SS
File Format	ASC118 UTF16ASUTF8
Action	Load Append
Field Qualifier
Reset If New Project	Yes/No
Allow String Overflow	Yes/No	If yes is selected, if a string of specified length overflows then the field will be switched to variable length string. "allowExisting": true
Trim Trailing Spaces	Yes/No	If yes is selected, trailing spaces will be removed from each data point. "trimTrailingSpaces": true
Ignore Existing	Yes/No	If yes is selected, and the specified table name already exists, the method will be skipped.
Create Table Only		If selected, will form an empty Definition and Loading section so that a table with no rows is created.

Item

Values

Description

Any string

Text to display in Script Editor Detail column

Select File

Brings up a Remote Data Source dialog

The Remote Data Source Dialog gives access to all remote files that have been uploaded to the DataJet File Server.

NOTE: To add files as a remote data source, see Admin | Remote Files | Remote File Manager

NOTE: Hold SHIFT to bring up file system on client machine. This is only valid if DataJet client and server are running on the same machine.

Convert To Load

Yes/No

TODO: What does this do?

Convert To Update

Yes/No

TODO: What does this do?

Delimiter

TAB
COMMA
SEMI-COLON
PIPE
HSTART

TODO - What is HSTART?

Date Format

YYYY-MM-DD
MM-DD-YYYY
DD-MM-YYYY
YYYYMMDD
MMDDYYYY
DDMMYYYY
M/D/Y
D/M/Y

DateTime Format

YYYY-MM-DD HH:MM:SS
DD-MM-YYYY HH:MM:SS
YYYY-MM-DD HH:MM
DD-MM-YYYY HH:MM
DDMMMYYYY HH:MM:SS

File Format

ASC118
UTF16ASUTF8

Action

Load
Append

Field Qualifier

Reset If New Project

Yes/No

Allow String Overflow

Yes/No

If yes is selected, if a string of specified length overflows then the field will be switched to variable length string.
"allowExisting": true

Trim Trailing Spaces

Yes/No

If yes is selected, trailing spaces will be removed from each data point.
"trimTrailingSpaces": true

Ignore Existing

Yes/No

If yes is selected, and the specified table name already exists, the method will be skipped.

Create Table Only

If selected, will form an empty Definition and Loading section so that a table with no rows is created.

{ "project": "#project#", "method": "CreateTableFromFile", "action": "LOAD", "table": "buildertemp", "filename": "%DATAPATH%/buildertemp.dat", "definition": [ "firstname|DISCRETE|STRING|BYTE", "surname|DISCRETE|STRING|BYTE", "email|DISCRETE|STRING|BYTE", "address|DISCRETE|STRING|BYTE" ], "loading": [ "firstname", "surname", "email", "address" ], "sample": false, "preHeaderSkip": 0, "dateFormat": "YYYY-MM-DD", "fileFormat": "ASCII8", "skipFirstLine": true, "delimiter": "TAB", "stripCharacter": "", "dateTimeFormat": "YYYY-MM-DD HH:MM:SS" }

{ "project": "#project#", "method": "CreateTableFromFile", "action": "LOAD", "table": "buildertemp", "filename": "%DATAPATH%/buildertemp.dat", "definition": [], "loading": [ "", "", "", "" ], "sample": false, "preHeaderSkip": 0, "dateFormat": "YYYY-MM-DD", "fileFormat": "ASCII8", "skipFirstLine": true, "delimiter": "TAB", "stripCharacter": "", "dateTimeFormat": "YYYY-MM-DD HH:MM:SS" }

{ "method": "CreateTableFromFile", "action": "LOAD", "table": "widetable1", "filename": "%DATAPATH%Wide/wide.dat", "definitionFile": "%DATAPATH%Wide/definition.xlsx", "dateFormat": "YYYY-MM-DD", "fileFormat": "ASCII8", "skipFirstLine": false, "delimiter": "TAB", "stripCharacter": "", "dateTimeFormat": "YYYY-MM-DD HH:MM:SS", "project": "wide", "sample": false }

CreateTableFromFile

Method Details

Context Menu

Examples

What's Next