MATLAB Answers

Importing | delimited data from Excel .xls and "Text to Columns"

44 views (last 30 days)
Brian
Brian on 14 Jun 2011
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 Comments

Gerd
Gerd on 14 Jun 2011
Hi Brian do you have an example? How is it possible to have 2 different 'types' in one excel cell
Brian
Brian on 14 Jun 2011
There are numbers in the data, but I guess that they could be imported as strings.
The data looks like: Part Number|||||||||||RL|Description|Obs Data Source|Calc Obs|Y to Obs

Sign in to comment.

Answers (2)

Bob Hamans
Bob Hamans on 14 Jun 2011
Have a look at the importdata function:
filename='myfile.xls';
A = importdata(filename,'|');

  6 Comments

Show 3 older comments
Brian
Brian on 15 Jun 2011
Today, I just installed R2011a. I changed test.txt to:
a | 1
b | 2
c | 3
a|b|c|d|e| f |123| 3e3e |
With results:
x =
data: [3x1 double]
textdata: {3x1 cell}
rowheaders: {3x1 cell}
>> x.textdata
ans =
'a |'
'b |'
'c |'
Do you know why this would happen?
Fangjun Jiang
Fangjun Jiang on 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
Fangjun Jiang on 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.

Sign in to comment.


Fangjun Jiang
Fangjun Jiang on 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 Comments

Show 4 older comments
Brian
Brian on 15 Jun 2011
I added an example in the comments for the original question.
Also, I tried your suggestion of x = importdata('test.txt','|') with the results:
x =
data: [3x1 double]
textdata: {3x1 cell}
rowheaders: {3x1 cell}
What causes the difference in our outputs?
Fangjun Jiang
Fangjun Jiang on 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
Bob Hamans on 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?

Sign in to comment.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!