Unable to open file as a workbook excel 97
50 views (last 30 days)
Show older comments
Steven Manz
on 13 Feb 2021
Commented: Jeremy Hughes
on 16 Feb 2021
I am attempting to import a list of .xls files and combine them. The issue is that the files were created in Excel 97-2003 and I have MATLAB version 2020b. Because of this, I get the following error:
Error using readtable (line 245)
Unable to open file '...12a.xls' as a
workbook. Check that the file exists, read access is available, and the file is a
valid spreadsheet file.
If I convert to a .txt file, the code can open the file and read it. The issue is when I convert from .xls to .txt, the .txt file combines all of the rows into one cell rendering the .txt file unusable. I believe this is caused because of the format of the .xls file.
I am curious if it is possible to read only certain rows and columns of the .xls file when converting to a .txt file to get rid of this issue or even to bypass needing the .txt file and somehow get MATLAB to read the .xls file properly.
For a better understanding, please run the following code on the attached files to see what I am speaking on (Look for the data values. The table is made properly on the first set of data. But on the second, things get weird. I want to only select the values listed out in the first data file and leave all the rest out as it is not important. Keep in mind that I have placed a bunch of 5's for confidentiality reasons, so disregard all of that.):
% Specify the desired path for the folder you want to operate on. You can
% simply copy and paste the directory from your file manager. Make sure to
% always add the '\' after the directory so the rest of the code knows
% where to start.
fileDir = '\';
% Import the raw data from the files in the directory
path_info = fullfile(fileDir, '*.xls');
files_temp = dir(path_info);
% Loop through each .out file, copy it and give new extension: .txt
for i = 1:numel(files_temp)
file = fullfile(fileDir, files_temp(i).name);
[tempDir, tempFile] = fileparts(file);
status = copyfile(file, fullfile(tempDir, [tempFile, '.txt']));
end
% Select all .txt files
path_info = fullfile(fileDir, '*.txt');
files = dir(path_info);
% Initialize parameters for the loop.
tables = [];
for i = 1:length(files)
% Read in the information from each data file and combine back into
% a single output file.
tables = readtable(files(i).name, 'VariableNamingRule', 'preserve');
tables
end
16 Comments
Mario Malic
on 15 Feb 2021
wkbk = [name '.xlsx']; % the output of this is below
wkbk = 'V22pw14a.xlsx'
This is the error, wkbk is not the full path to the file.
If you don't provide fullpath to the file you want to save, your file will be saved in C:\Users\<username>\Documents as mentioned in the comment above. If you go there, you'll see your file and if you use readtable on it, it'll work properly.
Better alternative for constructing the full path to files
[filepath, name, ext] = fileparts(files(1).name);
fullFilePath = fullfile(filepath, strcat(name, ext));
wkbk = 'C:\...\...\V22pw14a.xlsx' % this is how wkbk should look like
Accepted Answer
Mario Malic
on 15 Feb 2021
Moving conversation here as an answer.
For the reference, read the comment section.
hExcel = actxserver('Excel.Application');
Workbooks = hExcel.Workbooks;
Workbook = Workbooks.Open("C:\Users\<username>\Downloads\V22pw11a.xls");
Workbook.SaveAs("C:\Users\<username>\Downloads\V22pw12a.xlsx", 51); % It looks like format needs to be specified, without it, it won't be opened
Workbooks.Close;
t = readtable("C:\Users\<username>\Downloads\V22pw12a.xlsx");
t =
53×10 table
% not showing the table contents
To convert cell array to numeric array (if possible), you can use cellfun and str2num like shown below, keep in mind, to get the content of the table you index using curly brackets.
t{1,1};
1×1 cell array
{'-1.8'}
To get the content of cell array, index further into cell, use curly brackets again
t{1,1}{1}
ans =
'-1.8' % this is a char array
This is an example to get the numeric array from table
cellfun(@str2num, t{1:10,1});
However, I'd suggest you to read the documentation on readtable, it is probably possible to tweak its settings to read your file more easily.
More Answers (1)
Jeremy Hughes
on 14 Feb 2021
I tried opening one of your files in a hex editor, and it's not an excel file. It's a text file.
What clued me in was a warning when I tried to open the file with Excel.
How were these created?
BTW, this works:
T = readtable('V22pw12a.xls','FileType','text')
3 Comments
Jeremy Hughes
on 16 Feb 2021
Open the file in a text editor and you can see it's actually a tab delimited text file--the tool generating it is just labeling it .xls, but that's not what it is. I think the reason activeX is working is because that just opens Excel in the background and Excel is transforming the file. You could do this with readcell/writecell pretty easily.
C = readcell('V22pw12a.xls','FileType','text','Delimiter','\t')
writecell(C,'V22pw12a.xlsx')
But you don't really need to you. These read functions accept range for textfiles as well.
A1 = readmatrix('V22pw12a.xls','FileType','text','Delimiter','\t','Range','A39:J48')
A2 = readmatrix('V22pw12a.xls','FileType','text','Delimiter','\t','Range','A85:J89')
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!