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

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

[Gerd]

Hi Brian do you have an example? How is it possible to have 2 different 'types' in one excel cell

[Brian]

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

Answer 1

[Bob Hamans]

Have a look at the importdata function:

filename='myfile.xls'; 
A = importdata(filename,'|');

[Fangjun Jiang]

Good to know this argument. But I tried both .xls file and .txt file, it still read the whole string. See my answer below for example.

[Bob Hamans]

Hi Fangjun what Matlab version are you using? Unfortunately I cannot access the archived documentation to check the documentation for older Matlab releases and the functionality of importdata as my license does not allow for that. See http://www.mathworks.com/help/doc-archives.html and check for your version. I would be interested to hear if something changed and broke backward/forward compatibility of this function.

[Fangjun Jiang]

I have R2007b and R2010b. In R2007b, the result is like the one shown in my answer. In R2010b, it is like below so it should work for Brian.

x =

data: [3x1 double]

textdata: {3x1 cell}

rowheaders: {3x1 cell}

>> x.data

ans =

1

2

3

>> x.textdata

ans =

'a '

'b '

'c '

[Brian]

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]

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]

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.

Answer 2

[Fangjun Jiang]

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))

[Brian]

This was the problem that I was running into. There are about 10 MATLAB functions that are temptingly close to "Text to columns", but I have not found a way to do it.

[Fangjun Jiang]

Well, if your original file is in .txt file in my example, you can use

[y1,y2]=textread('test.txt','%s%d','delimiter','|') to get it. If your original file is in .xls file, then you have to use xlsread() or importdata() to get the data in a format like x above and then do porcessing.

[Brian]

I had the following error:

??? Error using ==> dataread

Number of outputs must match the number of unskipped input fields.

Error in ==> textread at 176

[varargout{1:nlhs}]=dataread('file',varargin{:});

[Fangjun Jiang]

What is your text file look like? Can you post an example? I ran the command and it didn't have any problem. Also, type help textread to look at the calling syntax of textread.

[Brian]

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]

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]

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?