preview

Preview eight rows from database using import options

Syntax

T = preview(opts)

Description

T = preview(opts) returns a table containing the first eight rows of database data by using the SQLImportOptions object. The value of the SelectedVariableNames property of the SQLImportOptions object specifies the variables that appear in the table.

Usually, the table contains eight rows of data. However, in some instances, the number of rows differs depending on property values defined in the SQLImportOptions object. The preview function returns fewer than eight rows if:

• The SQL query or table contains fewer than eight rows of data.

• The SQL query or table is empty or the MissingRule import option (of the variable import options) specifies to omit rows that contain missing data. To access the values of variable import options, use the getoptions function.

Examples

Preview Database Data After Customizing Options

Customize import options when importing text data from a database table. Control the import options by creating an SQLImportOptions object. Customize the import options for a text database column. Preview the database data before importing data. Then, import the data using the sqlread function.

This example uses the patients.xls spreadsheet, which contains the first column LastName. Also, the example uses a Microsoft® SQL Server® Version 11.00.2100 database and the Microsoft SQL Server Driver 11.00.5058.

Create a database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank user name and password.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');

Load patient information into the MATLAB® workspace.

patients = readtable('patients.xls');

Create the patients database table using the patient information.

tablename = 'patients';
sqlwrite(conn,tablename,patients)

Create an SQLImportOptions object using the patients database table and the databaseImportOptions function.

opts = databaseImportOptions(conn,tablename);

Retrieve the default import options for the LastName variable.

varnames = 'LastName';
varOpts = getoptions(opts,varnames)

varOpts = 
  SQLVariableImportOptions with properties:

  Variable Properties :
               Name: 'LastName'
               Type: 'char'
        MissingRule: 'fill'
          FillValue: ''

  String Properties :
     WhitespaceRule: 'preserve'
       TextCaseRule: 'preserve'

Set the import options for the data type of the LastName variable to string. Specify the LastName variable by using a numeric index that finds the variable within the SelectedVariables property of the SQLImportOptions object. Also, set the import options to replace missing data in the LastName variable with the NoName fill value.

index = 1;
opts = setoptions(opts,index,'Type','string', ...
    'FillValue','NoName');

Preview the first eight rows of database data using the import options. The data preview shows that the LastName variable has the string data type.

T = preview(opts)

T=8×10 table
     LastName      Gender     Age             Location              Height    Weight    Smoker    Systolic    Diastolic    SelfAssessedHealthStatus
    __________    ________    ___    ___________________________    ______    ______    ______    ________    _________    ________________________

    "Smith"       'Male'      38     'County General Hospital'        71       176        1         124          93              'Excellent'       
    "Johnson"     'Male'      43     'VA Hospital'                    69       163        0         109          77              'Fair'            
    "Williams"    'Female'    38     'St. Mary's Medical Center'      64       131        0         125          83              'Good'            
    "Jones"       'Female'    40     'VA Hospital'                    67       133        0         117          75              'Fair'            
    "Brown"       'Female'    49     'County General Hospital'        64       119        0         122          80              'Good'            
    "Davis"       'Female'    46     'St. Mary's Medical Center'      68       142        0         121          70              'Good'            
    "Miller"      'Female'    33     'VA Hospital'                    64       142        1         130          88              'Good'            
    "Wilson"      'Male'      40     'VA Hospital'                    68       180        0         115          82              'Good'            

Import the text data in the selected variable and display the first eight rows. The imported data shows that the variable has the string data type.

opts.SelectedVariableNames = 'LastName';
data = sqlread(conn,tablename,opts);
head(data)

ans=8×1 table
     LastName 
    __________

    "Smith"   
    "Johnson" 
    "Williams"
    "Jones"   
    "Brown"   
    "Davis"   
    "Miller"  
    "Wilson"  

Delete the patients database table using the execute function.

sqlquery = ['DROP TABLE ' tablename];
execute(conn,sqlquery)

Close the database connection.

close(conn)

Input Arguments

opts — Database import options

SQLImportOptions object

Database import options, specified as an SQLImportOptions object.

Version History

Introduced in R2019a