Lecture 13 Advanced Data Import Export Jeff Henrikson

Lecture 13 Advanced Data Import & Export Jeff Henrikson (jhenriks@umd. edu) http: //www. atmos. umd. edu/~jeff/aosc 347/ 1

Outline n File I/O n uiimport n import/export Excel 2 Copyright © 2015 University of Maryland. This material may not be reproduced or redistributed in whole or in part without written permission from Xin-Zhong Liang

File I/O. txt. xls. mat Read MATLAB from Write to . txt. xls. mat Problem areas: ■ Mixing character strings and numbers ■ Reading and writing formatted data a b 0. 0 288. 15 11. 0 216. 65 Show file extension: Finder -> Preferences. . . -> Advanced -> check box "Show all file extensions" 3 Copyright © 2015 University of Maryland. This material may not be reproduced or redistributed in whole or in part without written permission from Xin-Zhong Liang

Import/Export ASCII File with Known Data Format: Import Export n csvread n csvwrite n dlmread n dlmwrite n textscan n textread n fscanf Cell Array {. . } – access cellplot – visualize celldisp – display File with Unknown Data Format: n uiimport Import Wizard 4 Copyright © 2015 University of Maryland. This material may not be reproduced or redistributed in whole or in part without written permission from Xin-Zhong Liang

Read URL data urlreads in as an array of all single characters from the http url source file that contains both data and formatting text Work around data structure – strfind – find one string within another – length – get the length of a vector 5 Copyright © 2015 University of Maryland. This material may not be reproduced or redistributed in whole or in part without written permission from Xin-Zhong Liang

Data (mht=>txt=>xml) n Convert mht to txt data – File/Save as type: Text File Then delete any content not needed – Or copy the needed content to Notepad n Convert txt to xml data – Microsoft Office Excel – File/Open…. txt – Delimited Next> Tab & Space Finish 6 Copyright © 2015 University of Maryland. This material may not be reproduced or redistributed in whole or in part without written permission from Xin-Zhong Liang

Export to Excel We want to save the following table to an Excel file a 0. 0 b 288. 15 11. 0 216. 65 General format: xlswrite (filename, variable, Sheet, Range) 7 Copyright © 2015 University of Maryland. This material may not be reproduced or redistributed in whole or in part without written permission from Xin-Zhong Liang

Excel Data Structure xlswrite (filename, variable, Sheet, Range) 8 Copyright © 2015 University of Maryland. This material may not be reproduced or redistributed in whole or in part without written permission from Xin-Zhong Liang

Excel Data Structure xlswrite (filename, variable, Sheet, Range) Range = B 1 Data will be written starting at cell B 1, taking up as many rows and columns as required. Range = D 3: G 8 Data will be written in the box with corners specified by D 3 and G 8. Extra data will be discarded. 9 Copyright © 2015 University of Maryland. This material may not be reproduced or redistributed in whole or in part without written permission from Xin-Zhong Liang

Export to Excel Example Open new M-file and save as L 13 E 1. m close; clear; clc; % Define 2 column vectors a and b a=[0. 0; 11. 0]; a b 0 288. 15 b=[288. 15; 216. 65]; 11 216. 65 10 Copyright © 2015 University of Maryland. This material may not be reproduced or redistributed in whole or in part without written permission from Xin-Zhong Liang

Export to Excel Example % Write the header in the specified location xlswrite ('L 13 E 1. xls', 'a', 'Sheet 1', 'A 1'); xlswrite ('L 13 E 1. xls', 'b', 'Sheet 1', 'B 1'); % Write data in the specified location xlswrite ('L 13 E 1. xls', a, 'Sheet 1', 'A 2'); xlswrite ('L 13 E 1. xls', b, 'Sheet 1', 'B 2'); 11 Copyright © 2015 University of Maryland. This material may not be reproduced or redistributed in whole or in part without written permission from Xin-Zhong Liang

View Example Excel Output Current Directory Window Right click Open Outside MATLAB 12 Copyright © 2015 University of Maryland. This material may not be reproduced or redistributed in whole or in part without written permission from Xin-Zhong Liang

Write headers to Excel In L 13 E 1. m we wrote 1 -character headers a b 0 288. 15 11 216. 65 Let’s try writing nicer headers Height Temperature Level 1 0 288. 15 Level 2 11 216. 65 13 Copyright © 2015 University of Maryland. This material may not be reproduced or redistributed in whole or in part without written permission from Xin-Zhong Liang

Write headers to Excel MATLAB will write each character in a separate cell! We have to write text strings as cells, not arrays 14 Copyright © 2015 University of Maryland. This material may not be reproduced or redistributed in whole or in part without written permission from Xin-Zhong Liang

Excel header Example Open new M-file and save as L 13 E 2. m close; clear; clc; % Define 2 column vectors a and b a=[0. 0; 11. 0]; a b 0 288. 15 b=[288. 15; 216. 65]; 11 216. 65 15 Copyright © 2015 University of Maryland. This material may not be reproduced or redistributed in whole or in part without written permission from Xin-Zhong Liang

Create Excel header Cell Create a cell array with all headers Height Temperature Level 1 Level 2 % First create a 3 x 3 empty cell array head = cell (3, 3); % Then fill cells with row and column headers head{1, 2} = 'Height'; head{1, 3} = 'Temperature'; head{2, 1} = 'Level 1'; head{3, 1} = 'Level 2'; 16 Copyright © 2015 University of Maryland. This material may not be reproduced or redistributed in whole or in part without written permission from Xin-Zhong Liang

Excel header Example % Assign the Excel file name for output filename = 'L 13 E 2. xls'; % Write headers to Excel file xlswrite(filename, head, 'Sheet 1', 'A 1'); % Write data to Excel file xlswrite(filename, a, 'Sheet 1', 'B 2'); xlswrite(filename, b, 'Sheet 1', 'C 2'); 17 Copyright © 2015 University of Maryland. This material may not be reproduced or redistributed in whole or in part without written permission from Xin-Zhong Liang

View Example Excel Output Current Directory Window Right click Open Outside MATLAB 18 Copyright © 2015 University of Maryland. This material may not be reproduced or redistributed in whole or in part without written permission from Xin-Zhong Liang

Import from Excel >>uiimport [Enter] 1. Browse and select file L 13 E 2. xls 2. Click Names to preview data 3. 4. Check boxes to select data Click Finish 19 Copyright © 2015 University of Maryland. This material may not be reproduced or redistributed in whole or in part without written permission from Xin-Zhong Liang

uiimport Numeric variables are imported as array 'data' Text strings are imported as cells 'textdata' If you check Generate M-code, MATLAB will create an M-File to read the data. Next time, you can just type: >>importfile ('L 13 E 2. xls') [Enter] 20 Copyright © 2015 University of Maryland. This material may not be reproduced or redistributed in whole or in part without written permission from Xin-Zhong Liang

Import from Excel In Command Window >> whos [Enter] Name data textdata Size 2 x 2 3 x 3 Bytes 32 1070 Class double cell Attributes >> data [Enter] data = 0 11. 0000 288. 1500 216. 6500 >> textdata [Enter] textdata = '' 'Level 1' 'Level 2' 'Height' '' '' 'Temperature' '' '' 21 Copyright © 2015 University of Maryland. This material may not be reproduced or redistributed in whole or in part without written permission from Xin-Zhong Liang

Import from Excel uiimport works well if you have a single file Multiple data files: read data from inside the M-file (like textscan) Command to use: xlsread 22 Copyright © 2015 University of Maryland. This material may not be reproduced or redistributed in whole or in part without written permission from Xin-Zhong Liang

Import with xlsread >> mydata = xlsread ('L 13 E 2. xls', 'Sheet 1'); >> [mydata, header] = xlsread ('L 13 E 2. xls', 'Sheet 1') mydata = 0 11. 0000 288. 1500 216. 6500 header = '' 'Level 1' 'Level 2' 'Height' '' '' 'Temperature' '' '' 23 Copyright © 2015 University of Maryland. This material may not be reproduced or redistributed in whole or in part without written permission from Xin-Zhong Liang

Import Excel data file xlsread('filename. xls') n Before using this command, you need to ensure that where the file is stored is in your search path (File Set path) n If Excel is not installed on your computer, MATLAB cannot use xlsread or xlswrite 24 Copyright © 2015 University of Maryland. This material may not be reproduced or redistributed in whole or in part without written permission from Xin-Zhong Liang

xlswrite Exercise n Write code L 13 E 3. m: Use xlswrite to create L 13 E 3. xls for a table: Sport Name Score Wins Loses Ties University Soccer 12 7 2 University Hockey 15 6 3 University Baseball 10 5 4 Use cell arrays 25 Copyright © 2015 University of Maryland. This material may not be reproduced or redistributed in whole or in part without written permission from Xin-Zhong Liang

Cell Array Assignment c= 'r 1 c 1' 'r 2 c 1' 'r 3 c 1' clear; clc 'r 1 c 2' 'r 1 c 3' 'r 2 c 2' [] c = cell(3, 3); 'r 3 c 2' [] c{1, 2} = 'string' c(1, 2) = {'string'} % same as above c(1, : ) = {'r 1 c 1', 'r 1 c 2', 'r 1 c 3'} c{1, : } = {'r 1 c 1', 'r 1 c 2', 'r 1 c 3'} % wrong c(2: 3, 1) = {'r 2 c 1', 'r 3 c 1'} c(2: 3, 1) = {'r 2 c 1'; 'r 3 c 1'} % same as above c(2: 3, 1: 2) = {'r 2 c 1', 'r 2 c 2'; 'r 3 c 1', 'r 3 c 2'} 26 Copyright © 2015 University of Maryland. This material may not be reproduced or redistributed in whole or in part without written permission from Xin-Zhong Liang

Exercise Code % close file and clear workspace close; clear; clc; % Assign the output file name filename = 'L 13 E 3. xls'; % Create a cell array to hold all header info % -- First create a 4 x 4 empty cell array head = cell(4, 4); % -- Then fill cells with row and column headers head(1, : ) = {'Sport Name Score', 'Wins', 'Loses', 'Ties'}; head(2: 4, 1) = {'University Soccer'; 'University Hockey'; 'University Baseball'}; % Define a matrix of 3 columns by 3 rows to hold the scores score = [12, 7, 2; 15, 6, 3; 10, 5, 4]; % Write the headers to the Excel file xlswrite (filename, head, 'Sheet 1', 'A 1'); % Write data to the Excel file xlswrite (filename, score, 'Sheet 1', 'B 2'); 27 Copyright © 2015 University of Maryland. This material may not be reproduced or redistributed in whole or in part without written permission from Xin-Zhong Liang

Summary I/O n n n Interactive I/O with input Output with disp, fprintf I/O to/from *. mat files with save and load n n n Writing to ASCII file with fprintf Importing text with uiimport Importing text with textscan n Importing text with urlread n n n Exporting to Excel file with xlswrite Importing from Excel file with uiimport Importing from Excel file with xlsread 28 Copyright © 2015 University of Maryland. This material may not be reproduced or redistributed in whole or in part without written permission from Xin-Zhong Liang