Read data from CSV and Excel

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.

[Rik]

Why use xlsread, instead of dlmread, or even reading the raw text with fread?

[Eduardo Gaona Peña]

The CSV file contains important strings that I want to read as well.

[Rik]

Ah, yes, I keep forgetting that doesn't work. readtable should still be an option (if you have R2013b or newer)

And reading the raw text and parsing it with strsplit, textscan or any of those functions?

[Eduardo Gaona Peña]

awww it seemed like a good option. It seems that the function doesnt cannot fill the table with empty columns.....

[Jeremy Hughes]

Without seeing the file it's difficult to help. Can you show a sample of the file?

[Eduardo Gaona Peña]

Here is a screenshot of a part of the file. They basically contain multiple results, i.e., repeating headers.

[Walter Roberson]

Is it possible that the csv file was generated using commas as the decimal separator ?

[Eduardo Gaona Peña]

Oh sorry I didn't upload the screenshot of the CSV file. They are generated using ";" as delimiter and "." as decimal separator. Numbera like "0.568" are saved correctly using both the xlsx and the CSV files.

[Jeremy Hughes]

Closer to figuring this one out.

The first two lines with data in them should be skipped. You can pass 'HeaderLines' to readtable with the right number. (my guess is HeaderLines=14, based on the screenshot but a screen shot isn't as accurate as a real file--if you upload the actual CSV file it will help more)

Try this and find the smallest number of header lines that works:

T = readtable(filename,'HeaderLines',14)

If you still have issues, upload the file.

[Eduardo Gaona Peña]

mmm I dont know if skipping the headers would work for me. Depending on the settings of the test the rows where the headers are will change. Right now I'm also using them as a reference to count the number of tested cells:

[~,~,rawdata]=xlsread(strcat(PathName,FileName));
                    app.cellStartArray =find(strcmp(rawdata(:,1),'Cellnumber'));
                    app.numberCells=length(app.cellStartArray);
                    if app.numberCells>0
                    if app.NumericEditField15_2.Value<=0
                        warndlg('Enter an area value for the cells','No area value');
                         return
                    end
end

[Rik]

Why not do the parsing yourself then? If you use fread you can count the line ends before 'Cellnumber' if you like. Making a parser yourself might be less effort than trying to get these functions to work.

[Eduardo Gaona Peña]

I will try it out. First I have to understand how the fread works :P

[Rik]

I understand your hesitance, I had the same. Read the doc, try it out with a small file, read the file in binary (as opposed to text) and filter the end of line yourself (chars 10, 13, [10 13] or [13 10]). You should be fine.

For me it was a revelation how much you can do when go one step deeper and build your processing from basic tool output instead of the output of big tools like xlsread.

[Eduardo Gaona Peña]

Got it to work. fread/fgetl are really useful :). I can basically get the whole text, separate it as I want and put it into a table.

Thanks.

[Jeremy Hughes]

Thanks for uploading the file. I can now see why this is an issue.

fread and fgetl will work, you could also use textscan to read the data portions:

fid = fopen(filename)
if fid==-1
% file failed to open
end
while ~feof(fid)
    data = textscan(fid,'%f%f','Delimiter',';');
    % textscan stops when encountering the lines beginning with "Cellnumber" because it's not a number.
    line1 = textscan(fgetl(fid),'%s','Delimiter',';');
    line2 = textscan(fid,'%s%f%f%f%f%f%f%f%f',1,'Delimiter',';');
    % parse lines as needed.
end

Based on the example file, this should make it through all the data.

[chaw-long chu]

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]

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.

Answer 1

[Eduardo Gaona Peña]

Learning how to use fread, fgetl and such functions solved my issue.

[Eduardo Gaona Peña]

kudos to Rik Wisselink

Answer 2

[Helen Kirby]

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

[Eduardo Gaona Peña]

Yeah I would just do this, but the thing is that my boss would really like to just grab the CSV file and use it immediately :P.

[Helen Kirby]

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!