Error changing data in table from char to double
5 views (last 30 days)
Show older comments
*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
Accepted Answer
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
More Answers (1)
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
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!