% Program to read in data, the output is 2 matrices in the formats:
% IdentifierDistanceEndloc (type double):
% Column 1: Vehicle identifier, in format: House number.Vehicle number
% Column 2: Distance travelled in miles
% Column 3: 0 if the journey does not end at home or work, 1 if it does
% StartEnd (type datetime):
% Column 1: Start of journey
% Column 2: End of journey
% Read in the excel file, which has the following columns:
% 1. House number
% 2. Vehicle number
% 3. Start of journey, in format: dd/mm/yyyy HH:MM:SS
% 4. End of journey, in format: dd/mm/yyyy HH:MM:SS
% 5. Distance travelled in miles
% 6. Does the trip finish at home or work (1 for yes, 0 for no)
% Columns 1, 2, 5 and 6 are numbers and columns 3 and 4 are strings
% num reads the numbers and therefore contains 5 columns (with columns 3
% and 4 being blank)
% txt reads the strings and so contains 3 columns
[num,txt,~] = xlsread('JourneyData2Input.xlsx');
% Remove the 2 blank columns from the number vector so the HouseCarDistance
% contains the number values and add a vehicle number divided by 10 to the
% house number to give each vehicle a unique identifier in the format:
% House number.Vehicle number
IdentifierDistanceEndloc = [num(:,1)+(num(:,2)/10), num(:,5), num(:,6)];
% This step is to look for any dates that start at midnight, when reading
% in midnight values there is an error in the excel file
% For loops to cover all values in matrix
for i = 1:2
for j = 1:length(txt)
% Convert the cell to a string and count the number of characters,
% if the number of characters is less than 15 it means there is no
% time value and so the time should be midnight
if length(sprintf('%s',txt{j,i})) < 15
% Set a temporary value as current contents of the cell, this
% will only be a date
temp = sprintf('%s',txt{j,i});
% Combine the temp value with a time value for midnight and set
% this as the new cell value
txt(j,i) = {strcat(temp,' 00:00:00')};
end
end
end
% Convert the dates to the correct format for MATLAB
StartEnd = datetime(txt,'InputFormat','dd/MM/yyyy HH:mm:ss');
% Delete the values that are no longer required
clear num i j temp txt