Read data from CSV and Excel
39 views (last 30 days)
Show older comments
Eduardo Gaona Peña
on 7 Jan 2018
Commented: Walter Roberson
on 27 Sep 2019
Right now I'm developing an app that uses the data from a CSV file generated by a machine. The problem comes when I try to read the file using xlsread. The code works perfectly when using the .xlsx version of the file. All the values are read correctly, i.e., 1.753 will be regarded as a decimal. However, when I select the .csv version the aforementioned number will be read as 1753 (one thousand blablabla).
[FileName,PathName] = uigetfile({'*.xlsx;*.csv'},'File Selector');
[~,~,rawdata]=xlsread(strcat(PathName,FileName));
This is the part of the code that I use to select and read the file. On both cases all the data is placed on a cell array which I feel confortable with.
Does anyone know how can I make it work with the CSV file?. (The columns are separated with ';') My guess is that it's a problem of the default separator of windows, but who knows...
Thanks in advance.
17 Comments
chaw-long chu
on 27 Sep 2019
If my file is a multisheet structure and I only need to input one page, should i chop the file into different sections named with different file names and re-input it?
Any letters, description be accepted by the Matlab and only handle the numerical part?
Walter Roberson
on 27 Sep 2019
Csv files cannot be multi sheet.
If I recall correctly, xls files are binary files that contain internal information about where to find the sheets, so it is not necessary for programs to read all of the previous information to read a later sheet.
I would need to recheck how xlsx represents sheets. Xlsx is more portable than xls format, but not good at seeking within one sheet. I seem to recall that it separates sheets.
I don't think it is worth separating your sheets into different files for reading purposes. It can be worth separating them if you write to the file.
Accepted Answer
More Answers (1)
Helen Kirby
on 7 Jan 2018
I had trouble with reading in a csv file. I found it easier to open XL, select "file", drag down to "import", import the csv file then follow XL's (~4) pages of instructions to output the file as a .xlsx file. You get the chance to select your separators and delimiters. It's not complex. You will then have no problem reading that in (with an xlsread e.g. M = xlsread('your data.xlsx')).
2 Comments
Helen Kirby
on 8 Jan 2018
Yes, I agree, that was my goal too. I did actually call MatLab to see if there was an answer to this problem. I ended up more confused than I was to start with but maybe it will mean more to you:
Thank you for sending me the file! I think I know what the issue was.
If you open the file in Excel, you can see that each row occupies just one cell instead of being broken into multiple columns. That is why both "readtable" and Import Tool were importing all the data as just one column.
This can be fixed in Excel by breaking the text into multiple columns at the commas. You can find an example here:
https://support.office.com/en-us/article/Split-text-into-different-columns-with-the-Convert-Text-to-Columns-Wizard-30B14928-5550-41F5-97CA-7A3E9C363ED7
If you now save the resulting file (e.g. as 'jaybob.xls') both "readtable" and Import Tool would import the data with separated columns. For example:
>> mydata = readtable('jaybob.xls');
Now you can access the individual columns as follows:
>> mydata.Price
I am sorry for the confusion I introduced when talking about the 'Delimiter' option in "readtable". The reason we were getting an error is because 'Delimiter' is a valid parameter only on text files (like .CSV and .TXT), not on spreadsheet files (like .XLS). Options available for different types of files are described here:
https://www.mathworks.com/help/matlab/ref/readtable.html#input_argument_d119e887004
I will close the case for now, but please let me know if you have any trouble with importing this data or processing it in MATLAB. I would be happy to help!
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Products
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!