How do I reformat a mixed format data file to work with readtable()?

2 views (last 30 days)
I have a database of several hundred CSV files which I want to read using readtable(). The first 2 rows of these files contain textual information (strings), the next 3 rows are headers/units followed by tens of thousands of rows of corresponding data. The files are CSV format, however, readtable() does not recognize the file structure because the first two rows do not contain any commas. It reads all the data in, but places it into a m*n x 1 array rather than an m x n array.
In my attempts to troubleshoot this I noticed that if I would first open the CSV file in excel then re-save the file (as CSV), the first two rows would get a series of commas which enable readtable() to recognize the table format. It will work fine after that, however, doing this for my whole database is not practical. That being said, can someone help me to either;
a) configure readtable() to ignore the first two lines
b) read the CSV (maybe as a text file?) in and add commas to rows 1 and 2? Note: not all of the files have the same number of columns so its not as easy as setting up simple loop to do this. There would need to be some kind of smarts to determine how many columns should be there based on the lines 3 through end. That is where I get a little fuzzy.
I have attached a truncated sample file for reference.
Note: I am using R2015a.

Accepted Answer

Peter Perkins
Peter Perkins on 16 Jan 2018
In recent versions of MATLAB, you can use detectimportoptions to get more control over what gets read from where. But you may end up having to supplement readtable with a call to xlsread using a specific range to get some of your metadata.
  1 Comment
Matt Brown
Matt Brown on 16 Jan 2018
I guess I didn't realize that xlsread() would bring in CSV files. I tried it and it works perfectly. Exactly what I was looking for. Thank you.

Sign in to comment.

More Answers (0)


Find more on Tables in Help Center and File Exchange

Community Treasure Hunt

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

Start Hunting!