Error changing data in table from char to double

*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?

Answer 1

[Star Strider]

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.)

[Zac Lingen]

Thank you for taking to the time to look into my answer.

After running your code (Copied after this line), I returned the following error after the first line:

[~,S] = xlsread('DateAndQuantity.csv');
G_Q = sscanf([S{2:end,2}],'%d;');

Error using xlsred(line 257)

Unable to read XLS file /Users/..../DateAndQuantity.csv. File is not in recognized format.

I changed the format of the data I was using from .csv to .xls and the code converted successfully converted it into a double. I'm not sure if you changed the .csv to a .xls without mentioning it but either way I got it to work so thank you.

Thank you for also adding the additional work related to getting the dates. I'm not currently using them, but I plan to in the future so that will help me out. Thank you

[Star Strider]

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]

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

[Star Strider]

As always, my pleasure.

Answer 2

[A. Sawas]

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