Contents x
      LoadTableFromFile
      • 5 Minutes to read
      • Print
      • Dark
        Light
      • PDF
      Contents

      LoadTableFromFile

      • 5 Minutes to read
      • Print
      • Dark
        Light
      • PDF
      Article summary

      Article In progress...

      Loads data into an existing table structure that was created using CreateTableFromFile.   Will append data to existing data.

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

      Once the CreateTableFromFile method has been created using the Import Wizard, create a duplicate of it in script, making sure to place it after CreateTableFromFile:

      In the JSON window of the second CreateTableFromFile method, right-click, and choose CONVERT TO LOAD.  The JSON will be changed to the LoadTableFromFile format.


      CONVERT TO LOAD
      Note this is an irreversible action - it is not possible to convert from LoadTableFromFile back to CreateTableFromFile


      Verify: The table definition can be contained with the method JSON, or loaded from an excel spreadsheet.

      TODO: What is the difference between LoadTableFromFile and UpdateTableFromFile?


      Method Details

      KeyValue(s)Description
      projectactive project name
      methodLoadTableFromFileLoads data from file into an existing table object. 
      actionLOAD/APPENDLOAD - Creates new table object
      APPEND - Appends data to an existing table object.
      tableName of table to createTable must already exist.
      filenameFile(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.
      sourceFields[][
      "FieldName1",
      "FieldName2",
      "FieldName3", 
      "..."
      ] 
      		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 sourceFields section but is not included in the fields 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 CreateTableFromFile method that can converted into LoadTableFromFile,

      TODO: Does LoadTableFromFile support a definition file?

      LoadTableFromFile must contain either sourceFields+ fields, or definitionFile.
      fields[][
      "FieldName1",
      "FieldName2",
      "FieldName3", 
      "..."
      ] 
      		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.
      definitionFilePath/Filename.xlsxPath 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.
      sampletrue/falseIf "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.  
      sourceProject
      		TODO: What does this do?
      preHeaderSkip0/1TODO: What does this do?
      dateFormatYYYY-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

      fileFormatASC118
      UTF16ASUTF8      		TODO: What does this do?
      skipFirstLinetrue/falsetrue - file does not contain headers, skip first line
      false - file does contains a header, don't skip first line
      delimiter, ; | : COMMAAny consistent file delimiter is supported, including tab, spaces etc
      stripCharacterCharacter to removeAny and all instances of the character will be removed from the data as it is loaded
      dateTimeFormatYYYY-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
      YYYYMMDDHHMMSS

      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  

      YYYYMMDDHHMMSS - Available in v5.7.27 and later.

      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?
      autoScanResetIfNewtrue/false
      		Reset if new project - TODO:  What does this do?
      allowStringOverflowtrue/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. 
      ignoreExistingtrue/false
      		If true, and the specified table name already exists, the method will be skipped.
      onErrorIgnore
      		true/falseTODO: What does this do?
      completeFieldstrue/falseIf true, fields missing from the source file will be created as null.

      Context Menu

      ItemValuesDescription
      DescriptionAny stringText to display in Script Editor Detail column
      Select FileBrings up a Remote Data Source dialogThe 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.
      Include Sub Directories
      		Opens Remote Data Source Dialog
      TODO: How does this work?
      DelimiterTAB
      COMMA
      SEMI-COLON
      PIPE
      HSTART      		TODO - What is HSTART?
      Date FormatYYYY-MM-DD
      MM-DD-YYYY
      DD-MM-YYYY
      YYYYMMDD
      MMDDYYYY
      DDMMYYYY
      M/D/Y
      D/M/Y

      DateTime FormatYYYY-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
      YYYYMMDDHHMMSS

      File FormatASC118
      UTF16ASUTF8
      		TODO: What does this do?
      ActionLoad
      Append      		TODO: What does this do?
      Auto Scan -> Path"path"Opens a Remote Data Source dialog and allows a directory to be selected.
      "autoScanPath": "%DATAPATH%C19US"
      TODO: What does this do?
      Auto Scan -> On Complete Keep
      Rename
      Delete
      		"autoScanPost": "rename"
      TODO: What does this do?
      Field Qualifier

      Reset If New ProjectYes/No"autoScanResetIfNew": true
      Allow String OverflowYes/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 SpacesYes/No
      		If yes is selected, trailing spaces will be removed from each data point.
      "trimTrailingSpaces": true
      Ignore ExistingYes/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.


      Examples







      Example spreadsheet layout.  Note field zip4code, although present in the source data file, will not be loaded as it has a FieldDataType of SKIP:




      Was this article helpful?
      What's Next