Read Collection or Sequence of Spreadsheet Files - MATLAB & Simulink (original) (raw)

When you have data stored across multiple spreadsheet files, use spreadsheetDatastore to manage and import the data. After creating the datastore, you can read all the data from the collection simultaneously, or you can read one file at a time.

Data

If the folder C:\Data contains a collection of spreadsheet files, then capture the location of the data in location. The data used in this example contains 10 spreadsheet files, where each file contains 10 rows of data. Your results will differ based on your files and data.

location = 'C:\Data'; dir(location)

. .. File01.xls File02.xls File03.xls File04.xls File05.xls File06.xls File07.xls File08.xls File09.xls File10.xls

Create Datastore

Create a datastore using the location of the files.

ds = spreadsheetDatastore(location)

ds = SpreadsheetDatastore with properties:

                  Files: {
                         'C:\Data\File01.xls';
                         'C:\Data\File02.xls';
                         'C:\Data\File03.xls'
                          ... and 7 more
                         }

AlternateFileSystemRoots: {} Sheets: '' Range: ''

Sheet Format Properties: NumHeaderLines: 0 ReadVariableNames: true VariableNames: {'LastName', 'Gender', 'Age' ... and 7 more} VariableTypes: {'char', 'char', 'double' ... and 7 more}

Properties that control the table returned by preview, read, readall: SelectedVariableNames: {'LastName', 'Gender', 'Age' ... and 7 more} SelectedVariableTypes: {'char', 'char', 'double' ... and 7 more} ReadSize: 'file'

Read Data from Datastore

Use the read or readall functions to import the data from the datastore. If the data from the collection fits in the memory, then you can import it all at once using the readall function.

allData = readall(ds); size(allData)

Alternatively, you can import the data one file at a time using the read function. To control the amount of data imported, before you call read, adjust the ReadSize property of the datastore. You can set the ReadSize to 'file', 'sheet', or a positive integer.

If ReadSize is 'file', then each call to read returns data one file at a time.
If ReadSize is 'sheet', then each call to read returns data one sheet at a time.
If ReadSize is a positive integer, then each call to read returns the number of rows specified by ReadSize, or fewer if it reaches the end of the data.

ds.ReadSize = 'file'; firstFile = read(ds) % reads first file

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

'Smith'       'Male'      38     'County General Hospital'        71       176      'true'       124          93              'Excellent'       
'Johnson'     'Male'      43     'VA Hospital'                    69       163      'false'      109          77              'Fair'            
'Williams'    'Female'    38     'St. Mary's Medical Center'      64       131      'false'      125          83              'Good'            
'Jones'       'Female'    40     'VA Hospital'                    67       133      'false'      117          75              'Fair'            
'Brown'       'Female'    49     'County General Hospital'        64       119      'false'      122          80              'Good'            
'Davis'       'Female'    46     'St. Mary's Medical Center'      68       142      'false'      121          70              'Good'            
'Miller'      'Female'    33     'VA Hospital'                    64       142      'true'       130          88              'Good'            
'Wilson'      'Male'      40     'VA Hospital'                    68       180      'false'      115          82              'Good'            
'Moore'       'Male'      28     'St. Mary's Medical Center'      68       183      'false'      115          78              'Excellent'       
'Taylor'      'Female'    31     'County General Hospital'        66       132      'false'      118          86              'Excellent'

secondFile = read(ds) % reads second file

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

'Anderson'    'Female'    45     'County General Hospital'        68       128      'false'      114          77              'Excellent'       
'Thomas'      'Female'    42     'St. Mary's Medical Center'      66       137      'false'      115          68              'Poor'            
'Jackson'     'Male'      25     'VA Hospital'                    71       174      'false'      127          74              'Poor'            
'White'       'Male'      39     'VA Hospital'                    72       202      'true'       130          95              'Excellent'       
'Harris'      'Female'    36     'St. Mary's Medical Center'      65       129      'false'      114          79              'Good'            
'Martin'      'Male'      48     'VA Hospital'                    71       181      'true'       130          92              'Good'            
'Thompson'    'Male'      32     'St. Mary's Medical Center'      69       191      'true'       124          95              'Excellent'       
'Garcia'      'Female'    27     'VA Hospital'                    69       131      'true'       123          79              'Fair'            
'Martinez'    'Male'      37     'County General Hospital'        70       179      'false'      119          77              'Good'            
'Robinson'    'Male'      50     'County General Hospital'        68       172      'false'      125          76              'Good'

Read Collection or Sequence of Spreadsheet Files - MATLAB & Simulink (original) (raw)

Data

Create Datastore

See Also

Topics