CreateTableFromWorkbook

1 Minute to read

Print
Dark
Light
PDF

Article summary

Did you find this summary helpful?

Thank you for your feedback

Imports data from an excel workbook into a table

Key	Value(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"
headerRow	1	Row that contains the field names for the columns to create
startRow	0	First row of data to load. If 0, starts in row after header row.
endRow	0	Last row of data to load. If 0, all data is loaded.
ignoreEmptyRows	false	If 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.
allStringTypes	false	If true, all data will be loaded as string
leadingZeroAsStringType	false	If true, data with leading 0s (e.g., 0015242) will be loaded as string
minDiscreteSize	0	Optional. 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"
trimTrailingSpaces	false	If true, trailing spaces will be stripped as part of the load.
drop	true/false	Optional. Default = false. If false, and table exists, error will be raised. If true, drops "table" if it already exists.

example

{
  "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:

Browse for the workbook via the file icon (once selected a list of available worksheets will fill the combo box)
Select the required worksheet and specify table name.
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?

What's Next

CreateTableFromWorkbooks

Table of contents