How to prevent readtable converting numbers to date automatically when importing data? (and/or) How to import multiple data ranges with readtable?

Hello!

I ran into an issue using readtable with excel spreadsheets that contain data with mixed formats. My main data is basically a mix of dates and normal numbers. So for example the first three rows of each column contain dates followed by one row containing a plain number, then two lines with dates, one line plain number and so on.

When I use readtable to import, it automatically converts the plain numbers to dates (for example 10 -> 10-01-1900). Does anyone know how to prevent readtable from doing this automatic convertion?

I played around with readtable a bit but so far the only instances I found when readtable does not convert is when I import the rows containing the numbers seperately. But given the arrangement of the data in my files, I'd have to import each row containing normal numbers seperately, since readtable doesn't allow multiple ranges (would have to do the same for the rows containing dates). I'd rather want to avoid doing this.

So if anyone knows how to prevent readtable automatically converting my data OR how to import different rows of data simoultanously using readtable, I'd be most grateful.

[Harald]

Hi V,

the conversion may be due to the formatting of the cells in Excel. It would be easiest to investigate this if you could share a data example. Please also post the code lines you run.

I don't think you can import two different ranges with a single readtable command. This would require two separate commands.

Best wishes,

Harald

[V.]

I checked the formatting in Excel but the cells are clearly differently formatted (dates are formatted as "dates" and numbers as "standard").

Right now I'm not really running code, but I really have just being playing around with different import options. After fiddling around readtable seems to give me the least amount of issues. I may just have to bite the bullet and import rows separatly.

I attached an example file of my main data.

[Harald]

Hi,

tables are designed for data that has a consistent data type within each column. Your data has consistent data types within each row, but not within the columns. I would argue that for importing this kind of data, tables are not the best choice - at least not, if you want to keep the orientation of your data when working in MATLAB.

If within MATLAB, you are fine with working with column-oriented data, you could import this data as a cell array, transpose, then convert to a table.

data = readcell("test_nurDaten.xlsx")';
T = cell2table(data);

Best wishes,

Harald

[V.]

I tried readcell first, but ran into a whole different issue then (see my other question in my profile). So alas, readcell is out.

This is all quite frustrating because it's simple enough data, only the arrangement is a bit unusual though not super obscure. Unfortunately I can't do much pre-processing and swap rows and columns. Would probably make things easier.

[Harald]

I had missed that you are using R2020b. I don't have that release installed but confirm that things are tricky with R2020a. You can actually have MATLAB do the swapping of rows and columns for you and get a nice table:

[~, ~, raw] = xlsread("test_nurDaten.xlsx");
xlswrite("temp.xlsx", raw');
T = readtable("temp.xlsx");

The code I suggested earlier works in R2023b and is preferable in that release, since it does not require the writing of a temporary file.

data = readcell("test_nurDaten.xlsx")';
T = cell2table(data);

Thus if that's an option, I recommend upgrading to the latest release.

Best wishes,

Harald

[V.]

I managed to upgrade my version to the newest release (including swapping the language from German to English) and now readcell works just fine. Not sure if it's just the newest release or swapping the language solved my issue though (I think my initial issue with readcell was a lot due to language issues, but I can't be sure).

Anyways. Thanks for the help and tip with the version.

Answer 1

[Hassaan]

1. Specify Data Types for Each Column

You can instruct readtable to import each column as a specific data type. This can be done using the 'Format' parameter. Since your data is a mix of dates and numbers, you might consider importing the entire column as strings ('%s'). Afterward, you can programmatically convert the strings to the appropriate data type (dates or numbers) based on your knowledge of the data structure.

opts = detectImportOptions('yourfile.xlsx');
opts = setvartype(opts, 'char'); % or 'string', depending on your MATLAB version
data = readtable('yourfile.xlsx', opts);

% Then, programmatically convert cells to numbers or dates
for i = 1:height(data)
    for j = 1:width(data)
        if % your condition to check if it's a date or number
            % convert data{i, j} to date or number
        end
    end
end

2. Read the Entire Data as Text and Convert Afterwards

Another approach is to read the entire data as text and then use custom logic to parse each cell, converting them to numbers or dates as appropriate.

opts = detectImportOptions('yourfile.xlsx', 'TreatAsEmpty', {'NA', 'na'});
opts = setvartype(opts, repmat({'char'}, 1, width(opts))); % read everything as text
data = readtable('yourfile.xlsx', opts);

% Process the data to convert text to numbers or dates
for i = 1:height(data)
    for j = 1:width(data)
        currentValue = data{i, j}{1};
        if % your logic to determine if it's a date or number
            % Convert currentValue and assign back to data{i, j}
        end
    end
end

3. Use Low-Level File I/O Functions

If the above methods are not flexible enough, consider using lower-level file I/O functions (xlsread, fread, etc.) that give you more control over the reading process. This is more complex but allows you to handle data in a more customized way.

--------------------------------------------------------------------------------------------------------------------------------------------------------------

If you find the solution helpful and it resolves your issue, it would be greatly appreciated if you could accept the answer. Also, leaving an upvote and a comment are also wonderful ways to provide feedback.

Professional Interests

• Technical Services and Consulting
• Embedded Systems | Firmware Developement | Simulations
• Electrical and Electronics Engineering

Feel free to contact me.

[V.]

Thanks for the different suggestions! I'll try them and report back.

[Hassaan]

@Harald Sure. You are welcome.

--------------------------------------------------------------------------------------------------------------------------------------------------------------

If you find the solution helpful and it resolves your issue, it would be greatly appreciated if you could accept the answer. Also, leaving an upvote and a comment are also wonderful ways to provide feedback.

Professional Interests

• Technical Services and Consulting
• Embedded Systems | Firmware Developement | Simulations
• Electrical and Electronics Engineering

Feel free to contact me.