I want to read in an Excel table with heterogeneous data types

I can't find a format example, IO need to read in a data format, some string fields and double precision variables. What does the format option look like? e.g. %d. This is my code so far:

fname='Curves 02.13.18.xls';
T=readtable(fname,'ReadRowNames',true,format,{%d});
Bid = T(:,7);
Ask = T(:,8);
Mid = T(:,9);
Settle = T(1,1);

[Bob Thompson]

What are you trying to do with the data? Do you need to use readtable(), or can you get away with reading the entire file into a single array and sorting from there? I don't know if one is technically faster than the other, but I usually prefer not to use readtable() simply because I don't need the level of organization it provides.

[Julian Gammon III]

Thanks Bob for getting back. The file I am trying to read is only 23 records including a header record. There are some Date columns, some categorical columns (character string) and some floating point columns (real numbers). If I read using Readtable the spreadsheet goes into one Table, call it "T" which is 22 x 17. xlsread seems to give me the same thing. If I assign these columns to column vectors, i.e. Bid (:,1)= T(:,7); I cannot do basic arithmetic on the contents such as adding 2 elements together. I also tried assign the columns to Cell variable and it still did not work. How do I do these most basic of functions? Also I could not find after many hours of searching how to read in using a format spec, %D, etc. Also, I didn't say anything about sorting the input data, it is already in the correct order. This has been very frustrating,

Julian Gammon 212-823-3226

[Guillaume]

I usually prefer not to use readtable()

That sounds very misguided. readtable is extremely adept at parsing correctly almost any kind of excel file and if it doesn't then one of its options should set it on the right track.

[Guillaume]

@Julia,

• Which version of matlab? readtable has evolved and keep on evolving in each version, getting more and more flexible.
• Can you provide an example file? This will be the fastest way for us to find the correct readtable syntax.
• In general readtable can find the format of the columns on its own. If you use the Format option, you have to specify the format of each column. If you want to override the format of only one column, you'd use detectImportOptions instead.
• Is your header a row header or a column header? Column headers are more common.

edit: Guess I should have looked at the answers first. Peter has already made most of these points

[Julian Gammon III]

Thank you for getting back to me. Have been in meetings all am. I will attached the file. It is very small, 22 records, 17 columns. You can see the column geaders in row one. There is a mix of data and I want to be able to manipulate mathematicaly. It is in the correct order. Regards,

Julian

[Julian Gammon III]

Version is 2017b

[Guillaume]

Not sure if help is still needed since an answer has been accepted but for me, using R2017b

T = readtable('Curves 02.13.18.xls')

is enough to get the table read properly.

In any case, since the excel file does not have row headers, using 'ReadRowNames', true| was a mistake. 'ReadVariableNames', true would have been more appropriate but that's the default so is not needed.

Answer 1

[Brandon Armstrong]

One approach is to right-click on the data file in the Current Folder and select "Import Data." Then you can interactively select the data to import, set variable names, what to do with missing data, and what data-type to import each column. You can then use the Generate Script/Function button if you need to repeat this on spreadsheets saved the same way. I usually use this approach if I just need to import something once or if I don't care what the generated code looks like.

Another approach, which I use if I need to repeat the reading of the data, or keeping a record of how I read in the data is important is to use the detectImportOptions function. You can then modify the properties of this object to specify how the data should imported when using readtable. You can specify the number of header lines and data types and then pass the import options object to readtable.

Answer 2

[Peter Perkins]

Julian, Brandon's suggestions fo detectImportOptions and the Import Tool are good ones, but if all your spreadsheet has in it is text and numeric, it's likely that all you need to pass into readtable is the file name. Of course, financial data often has timestamps, but in recent versions of MATLAB, those are automatic as well. In earlier, you may need to read them as text and then convert.

Without an example of what's in the file, pretty hard to say.

[Julian Gammon III]

Thank you, but there are no Timestamps just dates. Here is what the file looks like, I have attached it.

[Peter Perkins]

By "timestamps", I meant dates or dates+times. In any case, in recent versions of MATLAB, you don't need to do anything special to read that spreadsheet:

>> t = readtable('Curves 02.13.18.xls');

Converting some of those variables to categorical is a good idea (you could also do that with detectimportoptions before reading):

>> t.CURRENCY = categorical(t.CURRENCY);
>> t.DAY_COUNT = categorical(t.DAY_COUNT);
>> t.FREQUENCY = categorical(t.FREQUENCY);
>> t
t =
  22×17 table
    VALUATION_DATE    SHIFTED_BY    CURRENCY    DAY_COUNT     FREQUENCY    DAILY_FIXING     BID       ASK       MID      ZC_BID    ZC_ASK    ZC_MID    DF_BID     DF_ASK     DF_MID       ID_DATE       ID  
    ______________    __________    ________    __________    _________    ____________    ______    ______    ______    ______    ______    ______    _______    _______    _______    ___________    _____
     13-Feb-2018          0           USD       Actual/360     Annual          1.42         1.396     1.446     1.421    1.4342    1.4342    1.4342    0.99882    0.99882    0.99882    15-Mar-2018    '1M' 
     13-Feb-2018          0           USD       Actual/360     Annual          1.42         1.485     1.535      1.51    1.5234    1.5234    1.5234    0.99742    0.99742    0.99742    16-Apr-2018    '2M' 
     13-Feb-2018          0           USD       Actual/360     Annual          1.42          1.53      1.58     1.555    1.5687    1.5687    1.5687     0.9961     0.9961     0.9961    15-May-2018    '3M' 
     13-Feb-2018          0           USD       Actual/360     Annual          1.42         1.557     1.607     1.582    1.5957    1.5957    1.5957    0.99468    0.99468    0.99468    15-Jun-2018    '4M' 
     13-Feb-2018          0           USD       Actual/360     Annual          1.42         1.604     1.654     1.629    1.6422    1.6422    1.6422    0.99314    0.99314    0.99314    16-Jul-2018    '5M' 
     13-Feb-2018          0           USD       Actual/360     Annual          1.42         1.637     1.687     1.662    1.6745    1.6745    1.6745    0.99164    0.99164    0.99164    15-Aug-2018    '6M' 
     13-Feb-2018          0           USD       Actual/360     Annual          1.42         1.718     1.768     1.743    1.7527    1.7527    1.7527    0.98688    0.98688    0.98688    15-Nov-2018    '9M' 
     13-Feb-2018          0           USD       Actual/360     Annual          1.42         1.793     1.843     1.818    1.8239    1.8239    1.8239    0.98183    0.98183    0.98183    15-Feb-2019    '1Y' 
     13-Feb-2018          0           USD       Actual/360     Annual          1.42         1.903     1.943     1.923    1.9328    1.9328    1.9328     0.9714     0.9714     0.9714    15-Aug-2019    '18M'
     13-Feb-2018          0           USD       Actual/360     Annual          1.42         1.994     2.044     2.019    2.0265    2.0265    2.0265    0.96001    0.96001    0.96001    18-Feb-2020    '2Y' 
     13-Feb-2018          0           USD       Actual/360     Annual          1.42        2.1205    2.1605    2.1405    2.1496    2.1496    2.1496    0.93733    0.93733    0.93733    16-Feb-2021    '3Y' 
     13-Feb-2018          0           USD       Actual/360     Annual          1.42        2.2032    2.2323    2.2178    2.2283    2.2283    2.2283    0.91456    0.91456    0.91456    15-Feb-2022    '4Y' 
     13-Feb-2018          0           USD       Actual/360     Annual          1.42        2.2542    2.2942    2.2742    2.2861    2.2861    2.2861    0.89182    0.89182    0.89182    15-Feb-2023    '5Y' 
     13-Feb-2018          0           USD       Actual/360     Annual          1.42        2.3447    2.3847    2.3647    2.3797    2.3797    2.3797    0.84617    0.84617    0.84617    18-Feb-2025    '7Y' 
     13-Feb-2018          0           USD       Actual/360     Annual          1.42        2.4377    2.4678    2.4527    2.4721    2.4721    2.4721    0.78076    0.78076    0.78076    15-Feb-2028    '10Y'
     13-Feb-2018          0           USD       Actual/360     Annual          1.42          2.49      2.49      2.49    2.5116    2.5116    2.5116    0.73953    0.73953    0.73953    15-Feb-2030    '12Y'
     13-Feb-2018          0           USD       Actual/360     Annual          1.42        2.5055    2.5455    2.5255    2.5494    2.5494    2.5494    0.68193    0.68193    0.68193    15-Feb-2033    '15Y'
     13-Feb-2018          0           USD       Actual/360     Annual          1.42         2.526     2.566     2.546    2.5688    2.5688    2.5688    0.59791    0.59791    0.59791    16-Feb-2038    '20Y'
     13-Feb-2018          0           USD       Actual/360     Annual          1.42        2.5408    2.5408    2.5408    2.5577    2.5577    2.5577    0.52723    0.52723    0.52723    17-Feb-2043    '25Y'
     13-Feb-2018          0           USD       Actual/360     Annual          1.42        2.5055    2.5455    2.5255    2.5339    2.5339    2.5339     0.4672     0.4672     0.4672    18-Feb-2048    '30Y'
     13-Feb-2018          0           USD       Actual/360     Annual          1.42        2.4903    2.4903    2.4903    2.4791    2.4791    2.4791    0.37066    0.37066    0.37066    15-Feb-2058    '40Y'
     13-Feb-2018          0           USD       Actual/360     Annual          1.42        2.4548    2.4548    2.4548    2.4214    2.4214    2.4214    0.29771    0.29771    0.29771    15-Feb-2068    '50Y'