Examples

In this section we provide some examples of expressions using the Data Import syntax.

1D/XY table

Suppose you have a set of files and you want to import the same physical quantities (from experimental measurements) as an XYs table from each file as in the image below.

The goal is to create a template that does not refer directly to columns A, E, and F. The template should contain an expression that refers to the content, i.e. the selection is always made on the crank angle, the load, and the load angle data of the second main bearings.

Create a template to automatically select the corresponding ranges as follows:

• X-axis

For the X-axis, the data range is the cells that contain the numerical values of the crank angle. The value of the selection is A6:A150. Let's replace A6 and A150 with an expression using the Data Import syntax. See Syntax for more information. Data range of the crank angle

A6 is the cell that is in the same column as the cell that contains the string crank angle but two rows further down. Using a combination of the FCELL and NEXTCELLR functions generates A6. FCELL("Crank Angle") returns A4 and NEXTCELLR(A4,2) returns A6. NEXTCELLR(FCELL("Crank Angle"),2) returns A6.

A150 can be written as A#150 using the # operator to define a cell from a column and a row. A is the column of the cell that contains the string Crank Angle and 150 is the last row of the spreadsheet that contains numerical values. Using a combination of the FCELL and COL functions generates A; and using the $LASTROW symbol generates 150. FCELL("Crank Angle") returns A4 and COL(A4) returns A. COL(FCELL("Crank Angle"))#$LASTROW returns A150.

You can replace in A6:A150, the cell values with the previous expressions. The : operator is kept to define the continuous range between the two cells. NEXTCELLR(FCELL("Crank Angle"),2):COL(FCELL("Crank Angle"))#$LASTROW

• Y-axes

For the Y-axes, the data range is the cells that contain the numerical values of the load and the load angle. The value of the selection is E6:E150 F6:F150. Let's replace E6, E150, F6, and F150 with an expression using the Data Import syntax. See Syntax for more information. Data range of the load

E6 is the cell that is in the same column as the cell that contains the string Load but two rows further down. But it is important to note that the string Load is used several times in the spreadsheet and only the one that refers to the second main bearing Main No. 2 is needed. The search for the cell that contains the string Load must be limited to a specific range. The range E2:G4 seems to be good. Using a combination of the FCELL function with limited range and the NEXTCELLR function generates E6. FCELL("Load",E2,G4) returns E4 and NEXTCELLR(E4,2) returns E6. NEXTCELLR(FCELL("Load",E2,G4),2) returns A6.

E150 can be written as E#150 using the # operator to define a cell from a column and a row. E is the same column as the column of the cell that contains the string Load and 150 is the last row of the spreadsheet that contains numerical values. Using a combination of the FCELL function in a limited range as previously and the COL function generates E; and using the $LASTROW symbol generates 150. FCELL("Load",E2,G4) returns E4 and COL(E4) returns E. COL(FCELL("Load",E2,G4))#$LASTROW returns E150.

You can replace in E6:E150, the cell values with the previous expressions. The : operator is kept to define the continuous range between the two cells. NEXTCELLR(FCELL("Load",E2,G4),2):COL(FCELL("Load",E2,G4))#$LASTROW

Let's replace E2 and G4 in the final expression above. E2 is the cell that contains the string Main No. 2. FCELL("Main No. 2") returns E2. G4 can be written as G#4 using the # operator to define a cell from a column and a row. G is the column before the column of the cell that refers to the third main bearing and 4 is the titles row. Using a combination of the FCELL, PREVCELLC and COL functions generates G; and using the $TITLESROW symbol generates 4. FCELL("Main No. 3") returns H2, PREVCELLC(H2,1) returns G2 and COL(G2) returns G. COL(PREVCELLC(FCELL("Main No. 3"),1)#$TITLESROW returns G4.

Data range of the load angle data

For the load angle of the second main bearing, the same expression as for the load can be used. Only the string Load has to be replaced by Load Angle in the function FCELL. NEXTCELLR(FCELL("Load Angle",E2,G4),2):COL(FCELL("Load Angle",E2,G4))#$LASTROW

The data range expression for the load and the load angle can be combined with the space operator to define a non-contiguous range.

Figure 35: Template example of an XY table

2D table

Suppose you have a set of files and you want to import the same physical quantities (from experimental measurements) as a 2D table from each file as in the image below.

The goal is to create a template that does not refer directly to the column A, the row 3, and the range B4:Z12. The template should contain an expression that refers to the content, i.e. the selection is always made on the duty cycle, the motor speed, and the output current data.

Create a template to automatically select the corresponding ranges as follows:

• X1-axis

For the X1-axis, the data range is the cells that contain the numerical values of the engine speed. The value of the selection is B3:Z3. Let's replace B3 and Z3 with an expression using the Data Import syntax. See Syntax for more information. Data range of the engine speed

B3 is the cell that is in the same row as the cell that contains the string DUTY CYCLE and one column further to the left. Using a combination of the FCELL and NEXTCELLC functions generates B3. FCELL("DUTY CYCLE") returns A3 and NEXTCELLR(A3,1) returns B3. NEXTCELLC(FCELL("DUTY CYCLE"),1) returns B3.

Z3 can be written as Z#3 using the # operator to define a cell from a column and a row. Z is the last column of the spreadsheet that contains numerical values and 3 is the line of the cell that contains the string DUTY CYCLE. Using a combination of the FCELL and ROW functions generates 3; and using the $LASTCOL symbol generates Z. FCELL("DUTY CYCLE") returns B3 and ROW(B6) returns 3. $LASTCOL#ROW(FCELL("DUTY CYCLE")) returns Z3.

You can replace in B3:Z3, the cell values with the previous expressions. The : operator is kept to define the continuous range between the two cells. NEXTCELLC(FCELL("DUTY CYCLE"),1):$LASTCOL#ROW(FCELL("DUTY CYCLE"))

• X2-axis

For the X2-axis, the data range is the cells that contain the numerical values of the duty cycle. The value of the selection is A4:A12. Let's replace A4 and A12 with an expression using the Data Import syntax. See Syntax for more information. Data range of the duty cycle

A4 is the cell that is in the same column as the cell that contains the string DUTY CYCLE and one row further down. Using a combination of the FCELL and NEXTCELLR functions generates A4. FCELL("DUTY CYCLE") returns A3 and NEXTCELLR(A3,1) returns A4. NEXTCELLR(FCELL("DUTY CYCLE"),1) returns A4.

A12 can be written as A#12 using the # operator to define a cell from a column and a row. A is the column of the cell that contains the string DUTY CYCLE and 12 is the last row of the spreadsheet that contains numerical values. Using a combination of the FCELL and COL functions generates A; and using the $LASTROW symbol generates 12. FCELL("DUTY CYCLE") returns A3 and COL(A3) returns A. COL(FCELL("DUTY CYCLE"))#$LASTROW returns A12.

You can replace in A4:A12, the cell values with the previous expressions. The : operator is kept to define the continuous range between the two cells. NEXTCELLR(FCELL("DUTY CYCLE"),1):COL(FCELL("DUTY CYCLE"))#$LASTROW

• Y data

For the Y data, the data range is the cells that contain the numerical values of the output current. The value of the selection is B4:Z12. Let's replace B4 and Z12 with an expression using the Data Import syntax. See Syntax for more information. Data range of the output current

B4 is the cell that is in the same column as B3 but one row further down, and B3 is the cell that is in the same row as the cell that contains the string DUTY CYCLE but one column further to the left. Using a combination of the FCELL, NEXCELLC and NEXTCELLR functions generates B4. FCELL("DUTY CYCLE") returns A3, NEXTCELLC(A3,1) returns B3, and NEXTCELLR(B3,1) returns B4. NEXTCELLR(NEXTCELLC(FCELL("DUTY CYCLE"),1),1) returns A4.

Z12 can be written as Z#12 using the # operator to define a cell from a column and a row. Z is the last column of the spreadsheet that contains numerical values and 12 is the last row of the spreadsheet that contains numerical values. Using the $LASTCOL symbol generates Z and the $LASTROW symbol generates 12. $LASTCOL#$LASTROW returns Z12.

You can replace in B4:Z12, the cell values with the previous expressions. The : operator is kept to define the continuous range between the two cells. NEXTCELLR(NEXTCELLC(FCELL("DUTY CYCLE"),1),1):$LASTCOL#$LASTROW

Figure 36: Template example of a 2D table

M1D table

Suppose you have a set of files and you want to import the same physical quantities (from experimental measurements) as a M1D table from each file as in the image below.

The goal is to create a template that does not refer directly to columns A, B, and F. The template should contain an expression that refers to the content, i.e. the selection is always made on the engine speed, the manifold pressure, and the IMEP data. The template executes an auto-detection based on these columns, so the option Detection is used.

Create a template to automatically select the corresponding ranges as follows:

• Detection range

For the detection range, the data range on which the detection is performed is those cells that contain the numerical values of the engine speed, the manifold pressure, and the IMPE. The value of the selection is A B F. Let's replace A, B and F with an expression using the Data Import syntax. See Syntax for more information. Engine speed

A is the column of the cell that contains the string Engine speed. Using a combination of the FCELL and COL functions generates A. FCELL("Engine speed") returns A1 and COL(A1) returns A. COL(FCELL("Engine speed")) returns A.

Manifold pressure

B is the column of the cell that contains the string Manifold pressure. Using a combination of the FCELL and COL functions generates B. FCELL("Manifold pressure") returns B1 and COL(B1) returns B. COL(FCELL("Manifold pressure")) returns B.

IMEP

F is the column of the cell that contains the string IMEP. Using a combination of the functions FCELL and COL generates F. FCELL("IMEP") returns F1 and COL(F1) returns F. COL(FCELL("IMEP")) returns F.

The expressions of the data ranges for the engine speed, the manifold pressure and the IMEP can be combined with the space operator to define a non-contiguous range. COL(FCELL("Engine speed")) COL(FCELL("Manifold pressure")) COL(FCELL("IMEP")) returns A B F.

Figure 37: Template example of a M1D table