3.7.5.100 Wbk.DC


The wbk.DC object has properties and methods related to the Data Connector attached to the workbook. The workbook object can be referred to using either "wbook" or "wbk".

Properties

Property Access Description
wbk.DC.mfiles

Read Only

Check if the workbook allows connections to multiple data sources.

Returns 1 for yes, and 0 for no

wbk.DC.nav

Read/Write

numeric

Create or remove data navigator: 0 = remove, 1 = create.

Note, to show/hide data navigator, please use page.cp.show.

wbk.DC.nConns

Read Only

Returns the number of connected sheets
wbk.DC.OCFiles$

Read Only

Returns the relative path of the connector cpp file to the Apps root folder. To construct the full OC file path, use

"%@A"+wbk.dc.OCFile$.

wbk.DC.optn$

Read/Write

Set partial import and sccript.
wbk.DC.source$

Read/Write

string

Get and set the data source. For example,
newbook;
wbook.dc.add("HTML");
wbook.dc.source$="https://www.extremeweatherwatch.com/cities/boston/year-1993";
wks.dc.sel$="Tables/_2";
wks.dc.import();

See also wks.DC.source$.

wbk.DC.type$

Read Only

Returns the connector type, CSV_Connector or JSON_Connector. This string can be used in wbk.DC.Add.
wbk.DC.URL$

Read Only

Returns the data source URL if connecting data from web.

Methods

Method Description
wbk.DC.add(file_type[,1]) Add Data Connector of file_type...

... where file_type is the name of the Connector (generally, just the name of the Connector, enclosed by double-quotes if there is a space in the name -- e.g. wbook.DC.add("Import Filter")). If in doubt, use the name of the Connector as it appears on the Connectors tab of the Apps Gallery (minus the word "Connector") and enclose file_type in double-quotes.

Note that in the case of some complex file structures (NetCDF, HDF), import times may be significantly reduced by not showing the Data Navigator pane. To prevent the Navigator pane from being added on import, add "1" as an argument:

wbk.dc.add("HDF",1);
wbk.DC.Compatible(file) check if given file is compatible with the current DC if book has one:

0 = not compatible
1 = compatible

int bValid = wbook.dc.Compatible(myfile$);
wbk.DC.import(nn) Import data:

nn = 0 or not specify: import all sheets in the workbook without opening the select... menu,
nn = 1: only import data for the active sheet. It will open Select... menu and then do the import.

wbk.DC.newsheet(sheet$) Connect to another sheet.
wbk.DC.remove(nn) Remove data connector.

nn = 0: Default value when nn is not specified. Remove data connector object from the current workbook, and remove edit protection from all destination sheets in this workbook.
nn = 1: Disconnect data source from the current worksheet and remove its edit protection.
nn = 2: Remove only eidt protection from the current worksheet, but connection will be kept.

Examples

EX1

Below codes import a HTML table on a wiki webpage to Origin worksheet.

newbook;
wbook.dc.add("HTML");
urllink$="https://en.wikipedia.org/wiki/List_of_metropolitan_statistical_areas";
wks.dc.source$={"isAdv":0,"bas":{"url":"%(urllink$)"},"adv":{"urlParts":["",""],"cmdTimeout":"","hdrParams":[]}};
wks.DC.Sel$=Tables/_1;
wks.dc.import();

EX2

The following example shows how to import an Excel file by Data Connector.

newbook;
wbook.dc.add("Excel");
wks.dc.source$=System.path.program$+"Samples\Import and Export\United States Energy (1980-2013).xls";
wks.dc.sel$="Natural Gas";
wks.dc.import();
//set column label rows
worksheet -s 0 1 0 1; 
wks.setaslabel(C, -1, 0, 0);
wks.setaslabel(L, -1, 0, 0);
wks.setaslabel(U, -1, 0, 0);

EX3

This example uses the Origin Connector to import data from another Origin project by running a query (information on the SQL-like query language supported by the Origin Connector can be found here).

// Origin project file location:
string path$ = "C:\temp\mydata.opju";
// Create new workbook in current project
newbook;
// Connect to the desired Origin project using the Origin Data Connector
wbook.dc.ADD("Origin");
wks.dc.source$ = path$;
// Specify query string to pull all X columns that have long name of "alpha"
wks.dc.sel$=Select Column from Project where (Column_LName like "alpha" AND Column_Type = X);
wks.dc.import();

EX4

This example uses the NetCDF Connector to do partial import of web-stored multi-dimensional data in a .nc file.

newbook mat:=1;
wbk.dc.add("NetCDF");
wks.dc.source$="https://psl.noaa.gov/thredds/fileServer/Datasets/cpc_us_precip/precip.V1.0.mon.mean.nc";
// import z=1 to end, read 1 skip 11; lon shift, lat flip, formula v*0.0393701
wks.dc.sel$=NetCDF/precip[1:0|1-11][y#][x/2]v*0.0393701;
wks.dc.import();

EX5

The following example shows how to specify an import filter in the same folder as your data file.

wbook.dc.add("Import Filter");
wks.dc.source$="C:\2016\Samples\Import and Export\S15-125-03.dat"; // connect to .dat file
wks.dc.sel$="test1.oif"; //use import filter "test1.oif" in the data folder - see Note
wks.dc.import();

Note: You can refer to an import filter saved to UFF\Filters using the %Y string register as in, for example, wks.dc.sel$="%YFilters\test1.oif";

EX6

This example can import Excel file with two sheets and setting headings

string fname$=system.path.program$ + "Samples\Import and Export\United States Energy (1980-2013).xls";
wbook.dc.add("Excel");
wks.dc.source$ = fname$;
Tree tr1 = wks.dc.Optn$;
tr1.Settings.mainheader = 2;
tr1.Settings.labels.SetAttribute("Use",1);
tr1.Settings.labels.longname=1;
tr1.Settings.labels.unit=2;
tr1.tostring(wks.dc.optn$);
wks.dc.sel$ = "Oil";
wks.dc.import();
//now connect to another sheet named "Coal"
wbook.dc.Newsheet("Coal");

EX7

The following 3 examples show you how to import tables from web.

//import ''table _2'' and then append ''table _9'' as new columns in the same worksheet
newbook;
wbook.dc.add("HTML");
wbook.dc.source$="https://www.extremeweatherwatch.com/cities/boston/year-1993";
wks.dc.sel$="Tables/_2";
wks.dc.import();
wks.DC.flags=0; //0: append new columns, 256: append new rows.
wks.dc.add("Tables/_9");


//import multiple tables in same sheet, for each table only import 2:3 columns. 
//table name only from 2nd char on and show as calendar 3 char month format 
newbook;
wbook.dc.add("HTML");
wbook.dc.source$="https://www.extremeweatherwatch.com/cities/boston/year-1993";
wks.dc.sel$="Tables/_2|Tables/_3|Tables/_4";
Tree tr = wks.dc.optn$;
tr.partial_col$="2 3";
tr.nameoptn$="2:0";//Keep table name from 2nd char on. Use "2" to show 2nd char only
ToString(wks.dc.optn$);
wks.dc.import();
wlabel ty:="table" fmt:="CM0"; //show calendar month Feb, Mar, Apr in Table row


//import multiple tables into same sheet by appending row after row
newbook;
wbook.dc.add("HTML");
wbook.dc.source$="https://www.extremeweatherwatch.com/cities/boston/year-1993";
wks.dc.sel$="Tables/_2|Tables/_3|Tables/_4";
wks.dc.flags=256;
Tree tr = wks.dc.optn$;
tr.partial_col$="2 3";
tr.ToString(wks.dc.optn$);
wks.dc.import();

See Also

Wks, Wks.DC