How to avoid trimming of string cells got by readtable method?

Hello, everyone,

I am working with spreadsheet tables, which I import by readtable method. I have several numeric columns, but my crucial point is the column with strings. The readtable function trim every string (removes leading and trailing whitespace). However, I need to avoid this trimming and leave the strings as they are. I tried to look into documentation, but found only the trimming related to tables originating from text files.

My current code related to this looks following:

mytable=readtable(filename,'Sheet',sheetname,'NumHeaderLines',0)

Do you have any suggestions?

[Cris LaPierre]

Can you share an example file that, when running the code you shared, produces the issue you describe?

[dpb]

@Cris LaPierre, any file such as the example @Matt J created will illustrate...the

tTest=readtable('tst.xlsx','readvariablenames',0)

tTest = 2×1 table
     Var1  
    _______

    {'dog'}
    {'cat'}

where the actual data in the spreadsheet are ' dog ' and ' cat'

This is setvaropts: <documented default behavior> for the 'WhiteSpaceRule' parameter for text variables (albeit it takes a fair amount of digging to get down that far given the veritable plethora of options <grin>)

Were you thinking there would/should be some other behavior?

[Cris LaPierre]

No, I was just trying to avoid creating my own test dataset.

[dpb]

We're all lazy, aren't we? <vbg>

I thought mayhaps you were getting ready to show us some magic!

Answer 1

[dpb]

The default is 'trim' for character variables, use the detectImportOptions object to set 'WhiteSpaceRule','preserve' for the character variable(s)...

opt=detectImportOptions(filename,'Sheet',sheetname);
opt=setvaropts(opt,opt.VariableNames(matches(opt.VariableTypes,'char')),"WhitespaceRule",'preserve');
tTable=readtable(filename,'Sheet',sheetname,opt);

and it will preserve the leading/trailing blanks.

Many options can be set at the command level, but 'WhitespaceRule' and such can only be set by calling setvaropts for the specific variables and functionality parameter desired. Then use the refined import options object to control the internals of how things are interpreted.

ADDENDUM:

Nota Bene that @Matt J's assignment syntax to the opts struct globally only works if there are only 'char' or 'string' variables; otherwise, the assignment will fail for numeric or other variable classes that don't have the specific option. Hence, the above code to assign by finding those that are 'char' variables to set only them.

[Petr]

Thank you very much! This was exactly what I needed. :-)

[dpb]

Glad to help...the myriad of detailed options to control input with the family of readtable and friends is able to solve most issues, but it takes a lot of in-depth study of the documentation to find them for the first time. Once one has seen it once, then know where to go looking for the next special occasion.

Answer 2

[Matt J]

filename='tst.xlsx';

opts = spreadsheetImportOptions(DataRange='A1');
opts=setvaropts(opts,WhiteSpaceRule='preserve');

mytable=readtable(filename,opts)

mytable = 2×1 table
      Var1   
    _________

    {' dog '}
    {' cat' }