CreateTableFromWorkbook
  • 1 Minute to read
  • Dark
    Light
  • PDF

CreateTableFromWorkbook

  • Dark
    Light
  • PDF

Article summary

Imports data from an excel workbook into a table

KeyValue(s)Description
method"CreateTableFromWorkbooks"Imports data from an excel workbook into a table
project"ProjectName"
worksheet"WorksheetName"Name of worksheet to load.
If provided, will load a specific worksheet name.
If missing, will load the first sheet.
table"TableName"Name of the table to create
action"LOAD"/"APPEND"
headerRow1Row that contains the field names for the columns to create
startRow0First row of data to load.  If 0, starts in row after header row.
endRow0Last row of data to load. If 0, all data is loaded.
ignoreEmptyRowsfalseIf true, empty rows will not be loaded.
worksheetRowName""If provided, stores the name of the worksheet that the record was loaded from.
filenameColumn""If provided, stores the workbook name that the record was loaded from.
allStringTypesfalseIf true, all data will be loaded as string
leadingZeroAsStringTypefalseIf true, data with leading 0s (e.g., 0015242) will be loaded as string
minDiscreteSize0Optional. 
Specify initial minimum discrete field size with key "minDiscreteSize": 1[2 or 4]
path"PathToFolder"The Path to the folder containing the workbooks
order"CREATED"
trimTrailingSpacesfalseIf true, trailing spaces will be stripped as part of the load.

sdsd

{
  "method": "CreateTableFromWorkbook",
  "action": "LOAD",
  "table": "ssd",
  "filename": "%DATAPATH%/details_precint.xlsx",
  "worksheet": "Sheet",
  "headerRow": 1,
  "startRow": 0,
  "endRow": 0,
  "worksheetRowName": "",
  "ignoreEmptyRows": true,
  "allStringTypes": false,
  "leadingZeroAsStringType": false
}

JSON right-click:

Excel Loader

Accessed through Script Editor | Import Data | Excel File, the Excel Loader loads a new table from a worksheet in an Excel workbook.

To load an excel file:

  1. Browse for the workbook via the file icon (once selected a list of available worksheets will fill the combo box)
  2. Select the required worksheet and specify table name.
  3. Optionally set the header start row and start and end rows to load. The default value for start and end rows is 0, this will load all available rows.

Tip:

If the workbook is not already on the server, upload it with the Remote File manager (Admin | Remote Files)

{
  "method": "CreateTableFromWorkbook",
  "action": "LOAD",
  "table": "mytable",
  "filename": "%DATAPATH%/a.xlsx",
  "worksheet": "Index",
  "headerRow": 1,
  "startRow": 0,
  "endRow": 0,
  "ignoreEmptyRows": true
}


{
  "method": "CreateTableFromWorkbook",
  "action": "LOAD",
  "table": "LondonStations_WIKI",
  "filename": "%DATAPATH%BikeData/BikeOverview/LondonStations.xlsx",
  "worksheet": "Sheet1",
  "headerRow": 1,
  "startRow": 0,
  "endRow": 0,
  "worksheetRowName": "",
  "ignoreEmptyRows": true,
  "allStringTypes": false,
  "leadingZeroAsStringType": false,
  "project": "BikeData2"
}






Was this article helpful?