MATLAB Answers

Error changing data in table from char to double

13 views (last 30 days)
Zac Lingen
Zac Lingen on 16 Apr 2019
Commented: Star Strider on 18 Apr 2019
*See attached data set for reference
I am importing the data set and trying to change the variable type for the second column, "GROSS_QTY" from a char to a double. I am following the example Matlab published (Here - "Detect and Use Import Options for Text Files").
Here is the code:
opts = detectImportOptions('DateAndQuantity.csv');
getvaropts(opts,{'GROSS_QTY'});
opts = setvartype(opts,{'GROSS_QTY'},'double');
opts.SelectedVariableNames = {'GROSS_QTY'};
qntyTable = readtable('DateAndQuantity.csv');
summary(T)
The summary outputs the following:
Variables:
GROSS_QTY: 105x1 cell array of character vectors
Per the example provided by Matlab, that variable should be a double. Can anyone explain what is happening?

  0 Comments

Sign in to comment.

Accepted Answer

Star Strider
Star Strider on 16 Apr 2019
Edited: Star Strider on 17 Apr 2019
The ‘GROSS_QTY’ problem is relatively easy to solve:
[~,S] = xlsread('DateAndQuantity.csv');
G_Q = sscanf([S{2:end,2}],'%d;');
I cannot get any of the date functions to import the dates correctly. I suspect the problems are the duplicated single quotes and perhaps that the months are in all capitalis.
EDIT —
I was able to import all except the first and last dates to a datetime array with this code:
S_Dv = strrep([S{2:end,1}], '''''', ',');
S_Ds = strsplit(S_Dv, ',');
D_S = cellfun(@(x)datetime(x, 'Format','dd-MMM-yyyy'), S_Ds(2:end-1))';
You may have to do those two manually. (NOTE the transpose to get them as a column vector.)

  4 Comments

Show 1 older comment
Star Strider
Star Strider on 18 Apr 2019
As always, my pleasure.
The conversions to useful data were something of an adventure!
I had no problem reading your file using the code I posted. It could be that we have different versions of Excel.
It would likely be best to import your file, do the necessary conversions, and then save it to a more convenient format, or as a ‘.mat’ file with the '-ascii' flag. The format you choose depends on whether other applications need to read it.
Zac Lingen
Zac Lingen on 18 Apr 2019
At the moment, the output of this script will be somewhat simple. However, I will eventually integrating this script's output into other applications so I will handle that problem at a later date.
Thank you again for your help

Sign in to comment.

More Answers (1)

A. Sawas
A. Sawas on 16 Apr 2019
Edited: A. Sawas on 16 Apr 2019
This is to explain what is happening when using the function detectImportOptions:
It seems the csv file format is inconsistent. Therefore, detectImportOptions is not able to guess the right format. The function only detects the comma ',' delimiter while the last column ends with semicolon ';' is not detected. To fix this issue, explicitly specify the delimiters.
However, this will cause the detectImportOptions to not correctly detect the columns names (because there is only a comma in the headers line) so you also need to specify them.
opts = detectImportOptions('DateAndQuantity.csv', 'Delimiter', {',', ';'});
opts.VariableNames(2) = {'GROSS_QTY'}; % specify the column name
opts.ExtraColumnsRule = 'ignore'; % do not add other columns than the selected one

  0 Comments

Sign in to comment.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!