CreateTableFromWorkbook
- 1 Minute to read
- Print
- DarkLight
- PDF
CreateTableFromWorkbook
- 1 Minute to read
- Print
- DarkLight
- 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. |
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:
- 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?