Importing xls file into an all-strings cell-array

Question

I'm trying to Import an xls file which has numeric as well as string data in it. Now I want the Output to be a cell-array consisting of strings only. I used

[numeric,text,raw]=xlsread('myFile.xls');

to read the file. Now I'm looking for a way to convert all cells of raw into strings. I actually solved the problem using

raw=cellfun(@convertmat2char,raw);

function charData = convertmat2char(data)
if isnumeric(data)
    charData={num2str(data)};
else
    charData={data};
end
end

but this is ridiculously slow. I guess this can be done in a pretty easy matrix operation, but I don't seem to be able to figure out how.

score 1 · Answer 1 · answered Mar 01 '17 at 14:31

1

Instead of checking whether it is numeric or not, directly use num2str on all of them.
Use cellfun and num2str like this:

raw = cellfun(@num2str, raw, 'UniformOutput', 0);

answered Mar 01 '17 at 14:31

Sardar Usama

19,536
9
36
58

I have to admit this a lot more elegant than what I was doing, but it doesn't seem to be any faster. As I stated in the question I'm looking for a fast solution if that is possible. Thanks for your help anyway, if there won't be any better Solutions, I'll accept your answer. – Max Mar 01 '17 at 14:38
@Max I don't think that would be possible. Although, if you are using *Windows OS* and have *Excel for Windows®*, you can save some time in using `xlsread` – Sardar Usama Mar 01 '17 at 14:51
@Sardar_Usama you mean the `xlsread` he's already using to generate the cell array? – sco1 Mar 01 '17 at 14:52
I justed timed your solution vs mine and on my files (they are pretty big) it's 160 seconds for your and 170 seconds for my solution which is not that big of a deal. What do you mean by 'using `xlsread`'? I am using `xlsread` – Max Mar 01 '17 at 14:53
@excaza What I am saying is using `xlsread` in basic import mode. – Sardar Usama Mar 01 '17 at 14:55
@Max Would it be possible to convert to CSV and use `csvread` or `dlmread`? `xlsread` is quite heavy. – rayryeng Mar 01 '17 at 14:56
@rayryeng I'm not sure if that is possible. I don't have a lot of files so it is no Problem converting them. But if I Export them to csv from Excel I might get Problems with the character that Excel will use as delimiter. If any field of the Excel file contains the delimiter (which will probably the case for any character) it will get messy. Or how would you suggest doing it? – Max Mar 01 '17 at 14:58
Do any of the strings have commas? If they don't, then exporting to CSV would be possible. Also, how many rows does your Excel sheet have? – rayryeng Mar 01 '17 at 15:00
@Max Probably you're not interested, but if the answer to my question is yes. I am very positive that `[numeric,text,raw]=xlsread('myFile.xls','','','basic')` would be faster – Sardar Usama Mar 01 '17 at 15:01
@rayryeng Yes, there are a lot of commas in the files. Also semicolons, spaces, Tabs and even some | – Max Mar 01 '17 at 15:01
@sardar_usama the xlsread is not the slow Thing. The conversion is slow. But what exactly does the Basic Import mode Change? – Max Mar 01 '17 at 15:03
@Max Still you didn't answer if you're using Windows and have Excel installed. If no then `xlsread` is already operating in basic import mode. Regarding what it is, I don't find it documented but it seems that without it, MATLAB first opens excel, do some processes and then closes it. Using basic import mode usually improves the data import – Sardar Usama Mar 01 '17 at 15:18
@Sardar_Usama sorry didn't read that question. I am using windows and I have excel. Still as I stated the import time is long (maybe like 15 seconds) but not the actual problem. The times that I mentioned (160 seconds vs. 170 seconds) are just for the 2 different conversion approaches without import. – Max Mar 01 '17 at 15:23

score 0 · Answer 2 · answered Mar 01 '17 at 15:12

0

How long does

nonNaNnumeric = numeric(~isnan(numeric));
num2str(nonNaNnumeric)

take? This times conversion of just the numbers to strings. If it isn't fast enough, you should try printf which is faster. If even that is too slow, you need to import data from xls as string in the first place to avoid this conversion, which I have no idea how to do or if it is even possible.

answered Mar 01 '17 at 15:12

Zizy Archer

1,392
7
11

I will give you feedback on this during the next few days. Since I just left my workplace and don't have access to the data right now. – Max Mar 01 '17 at 15:25

score 0 · Answer 3 · answered Jun 20 '23 at 10:34

0

Simply

rawString = string(raw);

answered Jun 20 '23 at 10:34

claudio

11
2

Importing xls file into an all-strings cell-array

3 Answers3