Unable to open file as a workbook excel 97
72 views (last 30 days)
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']));
% 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');
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
t = readtable("C:\Users\<username>\Downloads\V22pw12a.xlsx");
% 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.
1×1 cell array
To get the content of cell array, index further into cell, use curly brackets again
'-1.8' % this is a char array
This is an example to get the numeric array from table
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')