Importing | delimited data from Excel .xls and "Text to Columns"
5 Ansichten (letzte 30 Tage)
Ältere Kommentare anzeigen
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 Kommentare
Gerd
am 14 Jun. 2011
Hi Brian do you have an example? How is it possible to have 2 different 'types' in one excel cell
Antworten (2)
Bob Hamans
am 14 Jun. 2011
Have a look at the importdata function:
filename='myfile.xls';
A = importdata(filename,'|');
6 Kommentare
Fangjun Jiang
am 15 Jun. 2011
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
am 15 Jun. 2011
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
am 14 Jun. 2011
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 Kommentare
Fangjun Jiang
am 15 Jun. 2011
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
am 15 Jun. 2011
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?
Siehe auch
Kategorien
Mehr zu Spreadsheets finden Sie in Help Center und File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!