Importing | delimited data from Excel .xls and "Text to Columns"
5 次查看(过去 30 天)
显示 更早的评论
Hello, I am trying to import data from Excel (.xls). This data has a single column of mixed (numeric and string) data that is | (bar) delimited. My goal is to import this data and seperate each delimited portion into a seperate columns while maintaining the rows. Essentially, I am trying to do the Excel "Text to columns" with MATLAB. Thank you
2 个评论
Gerd
2011-6-14
Hi Brian do you have an example? How is it possible to have 2 different 'types' in one excel cell
回答(2 个)
Bob Hamans
2011-6-14
Have a look at the importdata function:
filename='myfile.xls';
A = importdata(filename,'|');
6 个评论
Fangjun Jiang
2011-6-15
I tested on R2010b and the results are same as yours. I have no idea. Maybe importdata() is not smart enough to handle inconsistent data format (the 4th line is certainly different than the first 3 lnies).
You need to provide a sample of data that is representative of your real data. In your comments on your question, you have consecutive delimiters and no numeric data. In your example above, you have lines that have different numbers of delimiters. What is your real data look like? What is your expected output?
Fangjun Jiang
2011-6-15
Maybe the following text form help importdata() will help.
For ASCII files and spreadsheets, IMPORTDATA expects to find numeric data in a rectangular form (that is, like a matrix). Text headers can
appear above or to the left of numeric data. To import ASCII files
with numeric characters anywhere else, including columns of character
data or formatted dates or times, use TEXTSCAN instead of IMPORTDATA.
When importing spreadsheets with columns of nonnumeric data, IMPORTDATA cannot always correctly interpret the column and row headers.
Fangjun Jiang
2011-6-14
Let's say my test.txt is like below
a | 1
b | 2
c | 3
x=importdata('test.txt','|')
x =
'a | 1'
'b | 2'
'c | 3'
Then you need to do some processing to get the data type you want.
x1=regexprep(x,'\|.+','')
x2=regexprep(x,'.+\|','')
y1=deblank(x1)
y2=str2num(char(x2))
7 个评论
Fangjun Jiang
2011-6-15
That must be that we are using different version of Matlab. I am using R2007b. You must use a new version. It looks like importdata('test.txt','|') could work as Bob Hamans suggested. Type x.data and x.textdata to see if that meets your need.
Bob Hamans
2011-6-15
I am still on R2008a here. Brian did I understand correctly my solution does work for you? Did you check the contents of x.data?
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!